<?php
/**
 *	Project:	Quicty: Quick application build environment depends on PEAR and Smarty
 *	File:		DataSet.class.php
 *
 *	@copyright	Tomoyuki Negishi and ZubaPitaTech, Inc.
 *	@author		Tomoyuki Negishi <tomoyu-n@zubapita.jp>
 *	@license	http://www.opensource.org/licenses/bsd-license.php The BSD License
 *	@package	Quicty
 *	@version	$Id:$
 */

// {{{ DataSet
/**
 *	DataSet Create, validate and process HTML forms with easy settings and O/R mapping class.
 *
 *	@author		Tomoyuki Negishi <tomoyu-n@zubapita.jp>
 *	@access		public
 *	@package	Quicty
 */
class DataSet extends DataView{
protected $pdo;
protected $use_pdo = true;
protected $__db_system_type;
protected $__transaction_mode;
protected $__dsn;
private $__ignore_no_table = false;
public $tables;
public $bind_form2column;
public $bind_column2form;

function DataSet($formName='', $method='post', $action='', $target='', $attributes=null, $trackSubmit=false) {
	DataView::DataView($formName, $method, $action, $target, $attributes, $trackSubmit);
	$this->data_set_config_dir = '/etc/data_set';
}

function bind_data_set($data_set_file,$config_dir='') {
	if(!$data_set) {
		if(!$config_dir) $config_dir = $this->__page_obj->home_dir.$this->data_set_config_dir;
		$data_set_array = $this->read_conf_to_array($config_dir,$data_set_file);
		$data_set = $data_set_array['root'];
	}
	foreach($data_set as $data_set_name=>$attributes) {
		if($data_set_name=='dsn') {
			$this->__dsn = $attributes;
			switch($this->__dsn['system']) {
				case 'pgsql':
					$this->escape_string_func = 'pg_escape_string';
					break;
				case 'mysql':
					$this->escape_string_func = 'mysql_real_escape_string';
					break;
				case 'sqlite':
				case 'sqlite3':
					$this->escape_string_func = 'sqlite_escape_string';
					break;
				default:
					$this->escape_string_func = 'addslashes';
			}
		} elseif($data_set_name=='bind') {
			$this->bind_table($attributes);
		} else {
			$this->tables[$data_set_name] = $attributes;
		}
	}
}

function escape_string_for_db($value) {
	if($this->__dsn['system']=='mysql') {
		if(!$this->mysql_link)  $this->connect_pdo($this->__dsn);
		$value = mysql_real_escape_string($value, $this->mysql_link);
	} else {
		$value = call_user_func($this->escape_string_func,$value);
	}
	return $value;
}

function esfd($value) {
	$value = $this->escape_string_for_db($value);
	return $value;
}

function bind_auth_set($data_set_file) {
	$data_set_array = $this->read_conf_to_array($this->data_set_config_dir,$data_set_file);
	foreach($data_set_array['root'] as $data_set_name=>$attributes) {
		if($data_set_name=='dsn') {
			$system = $attributes['system'];
			$dbname = $attributes['dbname'];
			$host = $attributes['host'];
			$port = $attributes['port'];
			$user = $attributes['user'];
			$password = $attributes['password'];
			if(!$system) {
				die('dsn: no system type! e.g.:pgsql');
			} else {
				$dsn = $system.'://';
				if($user) {
					$dsn .= $user;
					if($password) $dsn .= ':'.$password;
					$dsn .= '@';
				}
				if($host) {
					$dsn .= $host;
					if($port) $dsn .= ':'.$port;
				}
				if($dbname) $dsn .= '/'.$dbname;
			}
			$auth_params['dsn'] = $dsn;
		} elseif($data_set_name=='bind') {
			foreach($attributes as $auth_field=>$t_column) {
				list($table,$column) = explode('.' , $t_column);
				$auth_params['table'] = $table;
				$auth_params[$auth_field] = $column;
			}
		}
	}
	return $auth_params;
}


function new_auth_object($data_set_file,$strage_driver='DB') {
	$auth_params = $_FORM->bind_auth_set($data_set_file);
	$auth_obj = new Auth($strage_driver,$auth_params,NULL,false);
	return $auth_obj;
}


function bind_table($bind_array) {
	global $quicksmart_datatype;
	foreach($bind_array as $form_property=>$column_property) {
		list($form,$qs_type) = explode(':', $form_property);
		$hqf_type = $quicksmart_datatype[$qs_type]['hqf_type'];
		list($table,$column_and_type) = explode('.', $column_property);
		list($column,$column_type) = explode(':', $column_and_type);
		$this->bind_form2column[$form] = array(
			'qs_type'=>$qs_type,'hqf_type'=>$hqf_type,'table'=>$table,'column'=>$column,'column_type'=>$column_type);
		$this->bind_column2form[$table][$column] = array(
			'form'=>$form,'qs_type'=>$qs_type,'hqf_type'=>$hqf_type,'column_type'=>$column_type);
	}
}


function get_dsn($element='') {
	if($element) {
		return $this->__dsn[$element];
	} else {
		return $this->__dsn;
	}
}

function set_dsn($element,$value) {
	return $this->__dsn[$element] = $value;
}


//--------------------------------------------------------
//  Handling DataView status
//--------------------------------------------------------

function automatic_form_handler($condition='',$fields='*') {
	if($_GET[$this->id_column] AND !is_numeric($_GET[$this->id_column])) {
		$GET_ID_COLUMN = $_GET[$this->id_column];
		$GET_ID_COLUMN = "'$GET_ID_COLUMN'";
	} elseif($_GET[$this->id_column]) {
		$GET_ID_COLUMN = $_GET[$this->id_column];
	}
	if($_POST[$this->id_column] AND !is_numeric($_POST[$this->id_column])) {
		$POST_ID_COLUMN = $_POST[$this->id_column];
		$POST_ID_COLUMN = "'$POST_ID_COLUMN'";
	} elseif($_POST[$this->id_column]) {
		$POST_ID_COLUMN = $_POST[$this->id_column];
	}
	if($condition and strpos($condition,'where')===false)
		$condition = 'where '.$condition;
	switch($this->__page_obj->QuictyStatus) {
		case 'EDIT':
		case 'BROWSE':
		case 'DELETE_CONFIRM':
			if($condition) {
				$rows = $this->select_table($condition,$fields);
			} elseif($GET_ID_COLUMN) {
				$rows = $this->select_table('where '.$this->id_column.'='.$GET_ID_COLUMN,$fields);
			} else{
				$result['error'] = 'selectのwhere条件"'.$this->id_column.'"カラムが指定されていません';
				$rows = array();
			}
			$values_for_hqf = $this->form_table_value($rows);
			$this->setDefaults($values_for_hqf[0]); //各columnのフォームに値をセット
			$result['values'] = $values_for_hqf[0];
			$result['count'] = count($rows);
			break;
		case 'INSERT':
			$result = $this->save();
			break;
		case 'UPDATE':
			if($condition) {
				$result = $this->save($condition);
			} elseif($POST_ID_COLUMN) {
				$result = $this->save('where '.$this->id_column.'='.$POST_ID_COLUMN);
			} else {
				$result['error'] = 'selectのwhere条件"'.$this->id_column.'"カラムが指定されていません';
			}
			break;
		case 'DELETE':
			if($condition) {
				$result = $this->delete($condition);
			} elseif($POST_ID_COLUMN) {
				$result = $this->delete('where '.$this->id_column.'='.$POST_ID_COLUMN);
			} else {
				$result['error'] = 'selectのwhere条件"'.$this->id_column.'"カラムが指定されていません';
			}
			break;
	}
	return $result;
}

function automatic_batch_handler($condition='',$fields='*',$joined_fields=array()) {
	switch($this->__page_obj->QuictyStatus) {
		case 'EDIT':
		case 'BROWSE':
		case 'DELETE_CONFIRM':
			if(is_array($condition)) {
				$rows = $condition;
			} elseif($condition) {
				if($this->__pager_params['_use_pager']==true) {
					$rows = $this->select_table_with_pager($condition,$fields);
				} else {
					$rows = $this->select_table($condition,$fields);
				}
			} else{
				$result['error'] = $condition.'が指定されていません';
				$rows = array();
			}
			$table_values_for_hqf = $this->form_table_value($rows);
			if(is_array($table_values_for_hqf) and count($table_values_for_hqf)>0) {
				$renderer = new HTML_QuickForm_Renderer_ArraySmarty($this->__page_obj);
				foreach($this->_elements as $obj_key=>$element) {
					if(array_key_exists($element->_attributes['name'],$this->data_view)) {
						if($this->data_view[$element->_attributes['name']]['hide']==true) {
							unset($this->_elements[$obj_key]);
						} else{
							$form_elements[$element->_attributes['name']] = $element;
						}
					} elseif(array_key_exists($element->_name,$this->data_view)) {
						if($this->data_view[$element->_name]['hide']==true) {
							unset($this->_elements[$obj_key]);
						} else{
							$form_elements[$element->_name] = $element;
						}
					}
				}
	
				foreach($table_values_for_hqf as $key=>$values_for_hqf) {
					foreach($form_elements as $name=>$element) {
						//$current_name = "row_#_".$key."_#_$name";   #は変数名に使えない！
						$current_name = "row_".$key."_$name";
						$element->setName($current_name);
						if($values_for_hqf[$name]) {
							$set_values[$current_name] = $values_for_hqf[$name];
						} else {
							$set_values[$current_name] = '';
						}
					}
					$this->setDefaults($set_values);

					if(count($joined_fields)>0) {
						foreach($joined_fields as $column=>$attributes) {
							if(is_array($attributes)) {
								$form_rows[$key][$column]['label'] = $attributes['label'];
							} else {
								$column = $attributes;
							}
							$form_rows[$key][$column]['html'] = $values_for_hqf[$column];
							$form_rows[$key][$column]['value'] = $values_for_hqf[$column];
						}
					
					} else {
						$form_rows[$key] = array();
					}

					$this->accept($renderer);
					$form_rows[$key] = array_merge($form_rows[$key],$renderer->toArray());
					$form_rows[$key]['id']['value'] = $values_for_hqf['id'];
					foreach($this->data_view as $column=>$attributes) {
						if($attributes['reference']) {
							$form_rows[$key][$column]['html'] = 
								$this->__page_obj->get_value_from_table($form_rows[$key][$column]['value'],$attributes['reference']);
						}
					}
				}
			} else {
				$form_rows = array();
			}
			$result['values'] = $form_rows;
			$result['count'] = count($rows);
			break;
		case 'UPDATE':
			foreach($_POST as $param_name=>$attributes) {
				//if(substr($param_name,0,6)=='row_#_') {
				//	list($dmy,$row_no,$column) = explode('_#_',$param_name);
				if(substr($param_name,0,4)=='row_') {
					list($dmy,$row_no,$column) = explode('_',$param_name);
					if(!is_array($attributes)) {
						$update_array[$row_no][$column] = $this->esfd($attributes);
					} else {
						foreach($attributes as $key=>$value)
							$escaped_attributes[$key] = $this->esfd($value);
							$update_array[$row_no][$column] = $escaped_attributes;
					}
				}
			}
			foreach($update_array as $update_set) {
				$this->update_table($update_set,'where id='.$update_set['id']);
			}
			break;
		case 'DELETE':
/*
			if($condition) {
				$result = $this->delete($condition);
			} elseif($POST_ID_COLUMN) {
				$result = $this->delete('where '.$this->id_column.'='.$POST_ID_COLUMN);
			} else {
				$result['error'] = 'selectのwhere条件"'.$this->id_column.'"カラムが指定されていません';
			}
*/
			break;
	}
	return $result;
}


function hide_in_batch_list($fields=array()) {
	foreach($fields as $field_name) {
		$this->data_view[$field_name]['hide'] = true;
	}
}


function freeze_in_batch_list($fields=array()) {
	foreach($fields as $field_name) {
		$this->form_element[$field_name]->freeze();
	}
}

//--------------------------------------------------------
//  db Basic
//--------------------------------------------------------


function connect_pdo($dsn_array) {

	$system = $dsn_array['system'];
	$dbname = $dsn_array['dbname'];
	$host = $dsn_array['host'];
	$port = $dsn_array['port'];
	$user = $dsn_array['user'];
	$password = $dsn_array['password'];
	$persistent = (bool) $dsn_array['persistent'];
	$path = $dsn_array['path'];
	if(substr($path,0,1)!='/' and substr($path,1,2)!=':/') $path = $this->__page_obj->home_dir.'/'.$path;
	/*
	if(!$this->use_pdo = in_array($system,PDO::getAvailableDrivers())) {
		if (!extension_loaded($system)) {
			if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
				dl($system.'.dll');
			} else {
				dl($system.'.so');
			}
		}
	}
	*/
	try {
		if($system=='pgsql') {
			if(!$port) $port = '5432';
			$dsn_string = "host=$host port=$port dbname=$dbname user=$user";
			if($password) $dsn_string .= " password=$password";
			if($this->use_pdo) {
				$dsn = "pgsql:host=$host port=$port dbname=$dbname user=$user password=$password";
				$this->pdo = new PDO($dsn,$user,$password,array(PDO::ATTR_PERSISTENT => $persistent));
			} elseif($persistent) {
				$this->pdo = pg_pconnect($dsn_string)
					 or die('Could not connect: ' . pg_last_error());
			} else {
				$this->pdo = pg_connect($dsn_string)
					 or die('Could not connect: ' . pg_last_error());
			}
		} elseif($system=='mysql') {
			if(!$port) $port = '3307';
			$dsn = "mysql:dbname=$dbname;host=$host;port=$port";

			$this->pdo = new PDO($dsn,$user,$password,array(PDO::ATTR_PERSISTENT => $persistent));
			$this->mysql_link = mysql_connect($host, $user, $password);
		} elseif($system=='sqlite') {
			$dsn = "sqlite2:$path/$dbname";
			$this->pdo = new PDO($dsn,'','');
		} elseif($system=='sqlite3') {
			$dsn = "sqlite:$path/$dbname";
			$this->pdo = new PDO($dsn,'','');
		} else {
			$dsn = "$system:dbname=$dbname;host=$host;port=$port";
			$this->pdo = new PDO($dsn,$user,$password,array(PDO::ATTR_PERSISTENT => $persistent));
		}
		if(!$this->pdo) {
			die("Quicty error!:Can't connect database:$dsn\n");
		}
		if($this->use_pdo)
			$this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
		$this->__db_system_type = $system;
	} catch (PDOException $e) {
		$message = $e->getMessage();
		$this->log->err("$message.  source file:".__FILE__.' line:'.__LINE__);
	}
}

function close() {
	$this->close_pdo();
}

function close_pdo() {
	if($this->use_pdo) {
		$this->pdo = null;
	} elseif($this->__dsn['system']=='pgsql') {
		pg_close($this->pdo);
	}
}

function exec_sql($sql) {
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	try {
		if($this->use_pdo) {
			$stmt = $this->pdo->prepare($sql);
			$class_name = get_class($this->__page_obj);$GLOBALS['__debug_sql__'][] = array('call by'=>$class_name.' class','sql'=>$sql);
			$result['result'] = $stmt->execute();
			//$result['result'] = $this->pdo->query($sql);
			if($result['result']) {
				$result['count'] = $stmt->rowCount;
				$result['values'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
			}
		} else {
			switch($this->__db_system_type) {
				case 'pgsql':
					$result = pg_query($this->pdo,$sql) or die('Query failed: ' . pg_last_error());
					break;
			}
		
		}
	} catch (PDOException $e) {
		$message = $e->getMessage();
		//echo $message."\n";exit;
		if(strpos($message,'no such table')===false and $__ignore_no_table===false) {
			$this->log->err("$message. sql=$sql. source file:".__FILE__.' line:'.__LINE__);
		}
	}
	return $result;
}

function set_ignore_no_table($flag=true) {
	return $__ignore_no_table = $flag;
}

//--------------------------------------------------------
//  transaction wrapper method
//--------------------------------------------------------
function transaction_begin() {
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	$this->__transaction_mode = true;
	if($this->use_pdo) {
		$this->pdo->beginTransaction();
	} else {
		switch($this->__db_system_type) {
			case 'pgsql':
				pg_query( $this->pdo, "BEGIN TRANSACTION");
				break;
		}
	}
}

function transaction_commit() {
	if($this->use_pdo) {
		$this->pdo->commit();
	} else {
		switch($this->__db_system_type) {
			case 'pgsql':
				pg_query( $this->pdo, "COMMIT TRANSACTION");
				break;
		}
	}
	$this->__transaction_mode = false;
}

function transaction_end() {
	$this->transaction_commit();
}

function transaction_rollback() {
	if($this->use_pdo) {
		$this->pdo->rollback();
	} else {
		switch($this->__db_system_type) {
			case 'pgsql':
				pg_query( $this->pdo, "ROLLBACK TRANSACTION");
				break;
		}
	}
	$this->__transaction_mode = false;
}

function is_transaction() {
	return $this->__transaction_mode;
}
//--------------------------------------------------------
//  save(insert/ update) table
//--------------------------------------------------------
function save($condition='',$mergeFiles = true) {
	if($mergeFiles === true) {
		$values = HTML_QuickForm::arrayMerge($this->_submitValues, $this->_submitFiles);
	} else {
		$values = $this->_submitValues;
	}
	//print_r($values);
	if($condition) {
		$result = call_user_func(array($this,'update_table'), $values, $condition);
	} else {
		$result = call_user_func(array($this,'insert_table'), $values);
	}
	return $result;
} 


function insert_table($data) {
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	foreach($this->bind_column2form as $this->current_table=>$columns) {
		//print_r($columns);
		foreach($columns as $column=>$properties) {
			if(isset($data[$column])) {
				$data_type = $properties['hqf_type'];
				if($this->__force_values[$column]['call_function_last'] == 'yes') {
					eval($data[$column]);
					$data[$column] = $eval_result;
				}
				//echo "column=$column / data_type=$data_type<br>\n";
				$value = call_user_func(array($this,'save_'.$data_type.'_value'),$column,$data[$column]);
				if($data_type=='password' and $value===NULL) continue;
				//if($data_type=='file' and $value===NULL) continue;
				$icoumns_array[] = $column;
				$ivalues_array[] = $value;
				$result[$this->current_table][$column] = strip_quote($value);
			}
		}
		$icolumns = implode(',', $icoumns_array);
		$ivalues = implode(',', $ivalues_array);
		$sql = "INSERT INTO $this->current_table ($icolumns) VALUES ($ivalues)";
		$class_name = get_class($this->__page_obj);$GLOBALS['__debug_sql__'][] = array('call by'=>$class_name.' class','sql'=>$sql);
		//echo "sql=$sql<br>\n";
		$result[$this->current_table]['sql'] = $sql;
		try {
			if($this->use_pdo) {
				$result[$this->current_table]['count'] = $this->pdo->exec($sql);
				/*
				$stmt = $this->pdo->prepare($sql);
				if($stmt->execute())
					$result[$this->current_table]['count'] = 1;
				*/
				if($this->__db_system_type=='pgsql') {
					if($this->sequencer!='NULL') {
						if($this->sequencer) {
							$sequencer = $this->sequencer;
						} else {
							$sequencer = $this->current_table.'_id_seq';
						}
						$result[$this->current_table]['id'] = $this->pdo->lastInsertId($sequencer);
					}
					
				} else {
					$result[$this->current_table]['id'] = $this->pdo->lastInsertId();
				}
			} else {
				switch($this->__db_system_type) {
					case 'pgsql':
						$sequencer = $this->current_table.'_id_seq';
						if($this->is_transaction()) $already_transaction = true;
							else $this->transaction_begin();
						$rtn = pg_query( $this->pdo, $sql);
						$result[$this->current_table]['count'] = pg_affected_rows($rtn);
						$rtn = pg_query( $this->pdo, "SELECT CURRVAL('$sequencer') AS seq" );
						$data = pg_fetch_assoc( $rtn );
						pg_free_result($rtn);
						if($already_transaction) $already_transaction = false;
							else $this->transaction_commit();
						$result[$this->current_table]['id'] = $data[ 'seq' ];
						break;
				}
			}
			$result[$this->current_table]['state'] = true;
		} catch (PDOException $e) {
			$message = $e->getMessage();
			//echo $message."\n";exit;
			$this->log->err("$message. sql=$sql. source file:".__FILE__.' line:'.__LINE__);
			if($this->__transaction_mode) {
				$this->transaction_rollback();
				$result[$this->current_table]['state'] = false;
				$result[$this->current_table]['count'] = 0;
				return $result;
			}
		}
	}
	//var_check($result);
	return $result;
}

function update_table($data,$condition) {
	$condition = $this->esfd($condition);
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	foreach($this->bind_column2form as $this->current_table=>$columns) {
		$update_set_array = array();
		foreach($columns as $column=>$properties) {
			//echo "column=$column / data=".$data[$column]."<br>";
			if(isset($data[$column])) {
				$data_type = $properties['hqf_type'];
				if($this->__force_values[$column]['call_function_last'] == 'yes') {
					eval($data[$column]);
					$data[$column] = $eval_result;
				}
				//echo "column=$column / data_type=$data_type<br>\n";
				$value = call_user_func(array($this,'save_'.$data_type.'_value'),$column,$data[$column]);
				//echo "$column=$value<br>\n";
				if($data_type=='password' and $value===NULL) continue;
				if($data_type=='file' and $value===NULL) continue;
				$update_set_array[]= $column.'='.$value;
				$result[$this->current_table][$column] = strip_quote($value);
			}
		}
		if(count($update_set_array)<1) {
			die('Quicty error:there is no column in update set');
		}
		$update_set = implode(',',$update_set_array);
		$sql = "UPDATE $this->current_table SET $update_set $condition";
		$class_name = get_class($this->__page_obj);$GLOBALS['__debug_sql__'][] = array('call by'=>$class_name.' class','sql'=>$sql);
		//echo "sql=$sql<br>\n";;
		$result[$this->current_table]['sql'] = $sql;
		try {
			if($this->use_pdo) {
				$result[$this->current_table]['count'] = $this->pdo->exec($sql);
				/*
				$stmt = $this->pdo->prepare($sql);
				if($stmt->execute()) {
					$result[$this->current_table]['count'] = $stmt->rowCount;
				}
				*/
			} else {
				switch($this->__db_system_type) {
					case 'pgsql':
						$rtn = pg_query( $this->pdo, $sql);
						$result[$this->current_table]['count'] = pg_affected_rows($rtn);
						pg_free_result($rtn);
						break;
				}
			}
			$result[$this->current_table]['state'] = true;
		} catch (PDOException $e) {
			$message = $e->getMessage();
			$this->log->err("$message. sql=$sql. source file:".__FILE__.' line:'.__LINE__);
			if($this->__transaction_mode) {
				$this->transaction_rollback();
				$result[$this->current_table]['state'] = false;
				$result[$this->current_table]['count'] = 0;
				return $result;
			}
		}
	}
	//var_check($result);
	return $result;
}

function save_advcheckbox_value($column,$value) {
	$result = NULL;
	foreach($this->data_view[$column]['values'] as $key=>$t_value) {
		if($t_value==$value) {
			$result = $key;
			break;
		}
	}
	if($result===NULL) $result = $value;
	return $result;
}

function save_checkbox_value($column,$value) {
	return $value;
}

function save_date_value($column,$value) {
	if($value=='NULL') return 'NULL';
	if(is_array($value)) {
		$second = empty($value['s']) ? '00' : $value['s'];
		$minutes = empty($value['i']) ? '00' : $value['i'];
		$hour = isset($value['H']) ? $value['H'] : '00';
		$hour = isset($value['h']) ? $value['h'] : $hour;
		$ampm = isset($value['A']) ? $value['A'] : isset($value['a']) ? $value['a'] : NULL;
		$day = isset($value['d']) ? $value['d'] : NULL;
		$weekday = isset($value['D']) ? $value['D'] : isset($value['l']) ? $value['l'] : NULL;
		$month = isset($value['M']) ? $value['M'] : isset($value['F']) ? $value['F'] : isset($value['m']) ? $value['m'] : NULL;
		$year = isset($value['Y']) ? $value['Y'] : NULL;
		if($year) {
			$result = $year.'-'.$month.'-'.$day.' '.$hour.':'.$minutes.':'.$second;
		} else {
			$result = $hour.':'.$minutes.':'.$second;
		}
		if($ampm) $result .= ' '.$ampm;
	} else {
		$result = $value;
	}

	return "'$result'";
}

function check_text_or_numeric($column,$value) {
	$data_type = $this->bind_column2form[$this->current_table][$column]['column_type'];
	if($GLOBALS['quicty_data_column_type'][$data_type]=='numeric') {
		if(is_numeric($value)) {
			return $value;
		} else {
			return 'NULL';
		}
	} else {
		return "'$value'";
	}
}

function save_file_value($column,$value) {
	return NULL;
}


function save_hidden_value($column,$value) {
	$value = call_user_func($this->escape_string_func,$value);
	return $this->check_text_or_numeric($column,$value);
}

//function save_hidden_int_value($column,$value) {
//	return $value;
//}

function save_hireselect_value($column,$value) {
	$result = serialize($value);
	return $result;
}

//function save_int_value($column,$value) {
//	return $value;
//}

function save_password_value($column,$value) {
	if($value) {
		$md5pass = md5($value);
		return "'$md5pass'";
	} else {
		return NULL;
	}
}

function save_password_plain_value($column,$value) {
	$value = call_user_func($this->escape_string_func,$value);
	return $this->check_text_or_numeric($column,$value);
}

function save_radio_value($column,$value) {
	$result = "'".$value[$column]."'";
	return $result;
}

function save_select_value($column,$value) {
	if(is_numeric($value)) {
		return $value;
	} else {
		return "'$value'";
	}
}

function save_text_value($column,$value) {
	$value = call_user_func($this->escape_string_func,$value);
	return $this->check_text_or_numeric($column,$value);
}

function save_textarea_value($column,$value) {
	$value = call_user_func($this->escape_string_func,$value);
	return "'$value'";
}


//--------------------------------------------------------
//  delete table
//--------------------------------------------------------
function delete($condition='') {
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	foreach($this->bind_column2form as $table=>$columns) {
		$condition = $this->esfd($condition);
		$sql = "DELETE FROM $table $condition";
		$class_name = get_class($this->__page_obj);$GLOBALS['__debug_sql__'][] = array('call by'=>$class_name.' class','sql'=>$sql);
		$result[$this->current_table]['sql'] = $sql;
		try {
			if($this->use_pdo) {
				$result[$this->current_table]['count'] = $this->pdo->exec($sql);
				/*
				$stmt = $this->pdo->prepare($sql);
				if($stmt->execute())
					$result[$this->current_table]['count'] = $stmt->rowCount;
				*/
			} else {
				switch($this->__db_system_type) {
					case 'pgsql':
						$rtn = pg_query( $this->pdo, $sql);
						$result[$this->current_table]['count'] = pg_affected_rows($rtn);
						pg_free_result($rtn);
						break;
				}
			}
			$result[$table]['state'] = true;
		} catch (PDOException $e) {
			$message = $e->getMessage();
			$this->log->err("$message. sql=$sql. source file:".__FILE__.' line:'.__LINE__);
			if($this->__transaction_mode) {
				$this->rollback();
				$result[$table]['state'] = false;
				$result[$table]['count'] = 0;
				return $result;
			}
		}
	}
	return $result;
}

//--------------------------------------------------------
//  select table
//--------------------------------------------------------
function select($condition='',$fields='*') {
	return $this->select_table($condition,$fields);
}
function select_table($condition='',$fields='*') {
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	foreach($this->bind_column2form as $table=>$columns) {
		if($this->distinct_on) {
			$distinct = " DISTINCT ON($this->distinct_on)";
		}
		$sql = "SELECT$distinct $fields FROM $table $condition";
		$class_name = get_class($this->__page_obj);
		$GLOBALS['__debug_sql__'][] = array('call by'=>$class_name.' class','sql'=>$sql);
		#echo "sql=$sql<br>\n";
		try {
			if($this->use_pdo) {
				$stmt = $this->pdo->prepare($sql);
				if($stmt->execute())
					$table_values = $stmt->fetchAll(PDO::FETCH_ASSOC);
				//$table_values = $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
			} else {
				switch($this->__db_system_type) {
					case 'pgsql':
						$rtn = pg_query( $this->pdo, $sql);
						$table_values = pg_fetch_all($rtn);
						pg_free_result($rtn);
						break;
				}
			}
		} catch (PDOException $e) {
			$message = $e->getMessage();
			$this->log->err("$message. sql=$sql. source file:".__FILE__.' line:'.__LINE__);
		}
	}
	return $table_values;
}

function count_rows($condition='') {
	if(!isset($this->pdo)) $this->connect_pdo($this->__dsn);
	foreach($this->bind_column2form as $table=>$columns) {
		if($this->distinct_on) {
			$distinct = " DISTINCT ON($this->distinct_on)";
		}

		if($this->count_condition) {
			$count_condition = $this->count_condition;
		}

		if($this->real_count==true) {
			$sql = "SELECT$distinct $table.".$this->id_column." FROM $table $condition $count_condition";
		} elseif(strpos($condition,'join')!==false or strpos($condition,'JOIN')!==false) {
			$sql = "SELECT$distinct count(".$table.'.'.$this->id_column.") FROM $table $condition $count_condition";
		} else {
			$sql = "SELECT$distinct count(".$this->id_column.") FROM $table $condition $count_condition";
		}
		//echo "sql=$sql<br>";
		$class_name = get_class($this->__page_obj);$GLOBALS['__debug_sql__'][] = array('call by'=>$class_name.' class','sql'=>$sql);
		try {
			if($this->use_pdo) {
				$stmt = $this->pdo->prepare($sql);
				if($stmt->execute()) {
					$table_values = $stmt->fetchAll(PDO::FETCH_ASSOC);
					if($table_values[0]['count']) {
						$num = $table_values[0]['count'];
					} elseif($this->real_count==true) {
						$num = count($table_values);
					} else {
						$num = $table_values[0]['count('.$this->id_column.')'];
					}
					if($num==NULL) $num = 0;
				}
			} else {
				switch($this->__db_system_type) {
					case 'pgsql':
						$rtn = pg_query( $this->pdo, $sql);
						if($this->real_count==true) {
							$num = pg_num_rows($rtn);
						} else {
							$table_values = pg_fetch_all($rtn);
							$num = $table_values[0]['count'];
						}
						pg_free_result($rtn);
						break;
				}
			}
		} catch (PDOException $e) {
			$message = $e->getMessage();
			$this->log->err("$message. sql=$sql. source file:".__FILE__.' line:'.__LINE__);
			//var_check($e->getTrace());
		}
		//echo "num=$num<br>\n";
	}
	return $num;
}

//--------------------------------------------------------
//  select table with Pager
//--------------------------------------------------------

var $__pager_per_page = 20;
var $__pager_condition;
var $__pager_params;
var $__pager;


function bind_pager($params=array()) {
	$this->__pager_params['_use_pager'] = true;
	$this->__pager_condition = isset($params['condition']) ? $params['condition'] : '';
	$total_items = $this->count_rows($this->__pager_condition);
	$per_page = $params['perPage'] ? $params['perPage'] : $this->__pager_per_page;
	foreach($params as $key=>$value) {
		$this->__pager_params[$key] = $value;
	}
	if(!array_key_exists('importQuery',$params)) {
		$this->__pager_params['importQuery'] = true;
	}
	$this->__pager_params['perPage'] = $per_page;
	$this->__pager_params['totalItems'] = $total_items;
	if(substr($_SERVER['PHP_SELF'],-9)=='index.php') {
		$tmp = $_SERVER['PHP_SELF'];
		$_SERVER['PHP_SELF'] = $_SERVER['REQUEST_URI'];
		$this->__pager = Pager::factory($this->__pager_params);
		$_SERVER['PHP_SELF'] = $tmp;
	} else {
		$this->__pager = Pager::factory($this->__pager_params);
	}

	
}

function select_table_with_pager($condition='',$fields='*') {
	if($this->__pager_condition) 
		$condition = $this->__pager_condition.' '.$condition;
	$per_page = $this->__pager_params['perPage'];
	list($offset,$offset_end) = $this->__pager->getOffsetByPageId();
	$offset = $offset-1;
	$table_values = $this->select_table($condition.' limit '.$per_page.' offset '.$offset,$fields);
	return $table_values;
}

function get_pager_links() {
	$pager = $this->__pager->getLinks();
	$pager['currentPage'] = $this->__pager->getCurrentPageID();
	$pager['isFirstPage'] = $this->__pager->isFirstPage();
	$pager['isLastPage'] = $this->__pager->isLastPage();
	$pager['isLastPageComplete'] = $this->__pager->isLastPageComplete();
	$pager['numItems'] = $this->__pager->numItems();
	$pager['numPages'] = $this->__pager->numPages();
	return $pager;
}

function set_pager_per_page($num) {
	if($num) $this->__pager_per_page = $num;
	return $this->__pager_per_page;
}

//--------------------------------------------------------
//  get value from table DataSet.class.php version
//--------------------------------------------------------

// Quick build new data_view and data_set And object cache
//		DataSet.class.php version

public $memcached;
public $memcache_compress;
public $memcache_expire;

public function new_memcache() {
	if($this->memcached and class_exists('Memcache')) {
		isset($this->__page_obj->C['MEMCACHED_HOST'])
			? $memcached_host = $this->__page_obj->C['MEMCACHED_HOST']
			: $memcached_host = 'localhost';
		isset($this->__page_obj->C['MEMCACHED_PORT'])
			? $memcached_port = $this->__page_obj->C['MEMCACHED_PORT']
			: $memcached_port = '11211';
		$memcache = new Memcache;
		$memcache->connect($memcached_host,$memcached_port) or die ("memcachedに接続できませんでした");
	}
	return $memcache;
}


public function new_data_set($set_name) {
/*
	if($this->memcached) {
		if(!is_object($this->memcache)) $this->memcache = $this->new_memcache();
		$DATA_SET_CACHE = $this->memcache->get('DATA_SET');
	}
	if(isset($DATA_SET_CACHE[$set_name])) {
		$data_set = $DATA_SET_CACHE[$set_name];
	} else {
*/
		$data_set = $this->__page_obj->new_data_obj($set_name);
		$data_set->bind_data_view($set_name.'.view.conf');
		$data_set->bind_data_set($set_name.'.set.conf');	
/*
		$DATA_SET_CACHE[$set_name] = $data_set;
		if($this->memcached)
			$this->memcache->set('DATA_SET',$DATA_SET_CACHE,
					$this->memcache_compress,$this->memcache_expire);
	}
*/
	return $data_set;
}

function get_value_from_table($key,$tmp_array) {
	if($data_set = $tmp_array['_TABLE']) {
		$field = $tmp_array['_GET_COLUMN'];
		if(!is_object($this->reference_cache[$data_set]['object'])) {
			$this->reference_cache[$data_set]['object'] = $this->__page_obj->new_data_set($data_set);
			//$this->reference_cache[$data_set]['object'] = $this->new_data_set($data_set);
			/*
			$this->reference_cache[$data_set]['object'] = $this->__page_obj->new_data_obj($data_set);
			$this->reference_cache[$data_set]['object']->bind_data_view($data_set.'.view.conf');
			$this->reference_cache[$data_set]['object']->bind_data_set($data_set.'.set.conf');
			*/
		}
		if($this->reference_cache[$data_set][$key][$field]) {
			$result = $this->reference_cache[$data_set][$key][$field];
		} else {
			if(!is_numeric($key)) $key = "'$key'";
			$where = 'where '.$tmp_array['_WHERE_COLUMN'].'='.$key;
			$table = $this->reference_cache[$data_set]['object']->select_table($where,$field);
			$result = $table[0][$field];
			$this->reference_cache[$data_set][$key][$field] = $result;
		}
	} elseif($data_set = $tmp_array['_THIS_ARRAY']) {
		$array = $this->__page_obj->$data_set;
		if($key_2nd = $tmp_array['_2ND_COLUMN']) {
			if($key_3rd = $tmp_array['_3RD_COLUMN']) {
				$result = $array[$key][$key_2nd][$key_3rd];
			} else {
				$result = $array[$key][$key_2nd];
			}
		} else {
			$result = $array[$key];
		}
	}
	return $result;
}


//-----------------------------------------------------
// FORM表示用function
//-----------------------------------------------------
function make_form_of_column($column,$value) {
	$hqf_type = $this->data_view[$column]['type'];
	if(!$hqf_type) {
		$result = $value;
	} else {
		$result = call_user_func(array($this,'form_'.$hqf_type.'_value'),$column,$value);
	}
	return $result;
}

function form_table_value($table_values) {
	if(is_array($table_values)) foreach($table_values as $key=>$row) {
		$new_row = array();
		foreach($row as $column=>$value) {
			$column = $this->get_column_name($column);
			$new_row[$column] = $this->make_form_of_column($column,$value);
		} 
		if($this->__page_obj->QuictyStatus=='EDIT' and count($this->__force_values)) {
			foreach($this->__force_values as $column=>$force_values) {
				if($force_values['auto_only_1st']!='yes') {
					if($force_values['call_function']=='yes') {
						$new_row[$column] = eval($force_values[$column]);
					} else {
						$new_row[$column] = $force_values[$column];
					}
				}
			}
		}
		$form_table[$key] = $new_row;
	}
	return $form_table;
}

function form_advcheckbox_value($column,$value) {
	return $this->data_view[$column]['values'][$value];
}

function form_checkbox_value($column,$value) {
	return $value;
}

function form_date_value($column,$value) {
	if(!$value and $td=$this->__default_values[$column]) {
		$value = $td['Y'].'-'.$td['m'].'-'.$td['d'].' '.$td['H'].':'.$td['i'].':'.$td['s'];
	}
	$da = strptime($value,'%Y-%m-%d %H:%M:%S');
	$timestamp = mktime($da['tm_hour'],$da['tm_min'],$da['tm_sec'],$da['tm_mon']+1,$da['tm_mday'],$da['tm_year']+1900);
	list($year,$mon_short,$mon_full,$month,$wday,$wday_full,$day,$hour_24,$hour_12,$minutes,$second,$am_lower,$am_upper) 
		= explode(',', date("Y,M,F,m,D,l,d,H,h,i,s,a,A",$timestamp));
	$result =  array('Y'=>$year,'M'=>$mon_short,'F'=>$mon_full,'m'=>$month,'D'=>$wday,'l'=>$wday_full,'d'=>$day,'H'=>$hour_24,'h'=>$hour_12,'i'=>$minutes,'s'=>$second,'a'=>$am_lower,'A'=>$am_upper);
	return $result;
}

function form_file_value($column,$value) {
	return $value;
}

function form_hidden_value($column,$value) {
	return $value;
}

function form_hidden_int_value($column,$value) {
	return $value;
}

function form_hireselect_value($column,$value) {
	return unserialize($value);
}

function form_int_value($column,$value) {
	return $value;
}

function form_password_value($column,$value) {
	return NULL;
}

function form_password_plain_value($column,$value) {
	return $value;
}

function form_radio_value($column,$value) {
	return array($column=>$value);
}

function form_select_value($column,$value) {
	return $value;
}

function form_text_value($column,$value) {
	return $value;
}

function form_textarea_value($column,$value) {
	return $value;
}



//-----------------------------------------------------
// TABLE表示用function
//-----------------------------------------------------

function make_body_of_column($column,$value) {
	$qs_type = $this->data_view[$column]['qs_type'];
	if(!$qs_type) {
		$result = $value;
	} else {
		$result = call_user_func(array($this,'display_'.$qs_type.'_value'),$column,$value);
	}
	return $result;
}

function get_column_name($column) {
	if($p = strpos($column,'.')) {
		$column = substr($column,$p+1);
	}
	return $column;
}

function set_display_row_values($column,$value) {
	$new_row['body'] = $this->make_body_of_column($column,$value);
	$new_row['html'] = $new_row['body'];
	$new_row['label'] = $this->data_view[$column]['label'];
	$new_row['type'] = $this->data_view[$column]['type'];
	$new_row['qs_type'] = $this->data_view[$column]['qs_type'];
	$new_row['value'] = $value;
	return $new_row;
}


function display_table_value($table_values) {
	$sequence = 1;
	if($this->__pager_params['_use_pager']) {
		list($offset,$offset_end) = $this->__pager->getOffsetByPageId();
		$offset = $offset-1;
		$sequence += $offset;
	}
	if(is_array($table_values) and count($table_values)>0) {
		foreach($table_values as $key=>$row) {
			$new_row = array();
			$new_row['sequence']['value'] = $sequence;
			foreach($row as $column=>$value) {
				$column = $this->get_column_name($column);
				if(is_array($value)) {
					$new_sub_row = array();
					foreach($value as $sub_key=>$sub_row) {
						if(is_array($sub_row)) {
							foreach($sub_row as $sub_col=>$sub_val) {
								$new_sub_row[$sub_key][$sub_col] = $this->set_display_row_values($sub_col,$sub_val);
							}
						} else {
							$new_sub_row = $this->set_display_row_values($sub_key,$sub_row);
						}
					}
					$new_row[$column] = $new_sub_row;
				} else {
					$new_row[$column] = $this->set_display_row_values($column,$value);
				}
			} 
			$sequence++;
			$display_table[$key] = $new_row;
		}
	}
	return $display_table;
}

function display_advcheckbox_value($column,$value) {
	$list = $this->data_view[$column]['values'];
	$result = $list[$value];
	return $result;
}

function display_checkbox_value($column,$value) {
	return $value;
}

function display_date_value($column,$value) {
	$format = $this->data_view[$column]['opt']['format'];
	$result = date($format,strtotime($value));
	return $result;
}

function display_float_value($column,$value) {
	return $value;
}

function display_file_value($column,$value) {
	return $value;
}

function display_hidden_value($column,$value) {
	if($this->data_view[$column]['reference']) {
		$result = $this->get_value_from_table($value,$this->data_view[$column]['reference']);
	} else {
		$result = $value;
	}
	return $result;
}

function display_hidden_int_value($column,$value) {
	if($this->data_view[$column]['reference']) {
		$result = $this->get_value_from_table($value,$this->data_view[$column]['reference']);
	} else {
		$result = $value;
	}
	return $result;
}

function display_hireselect_value($column,$value) {
	$list = $this->data_view[$column]['list'];
	$result = $list[$value];
	return $result;
}

function display_id_value($column,$value) {
	return $value;
}

function display_int_value($column,$value) {
	return $value;
}

function display_password_value($column,$value) {
	return NULL;
}

function display_password_plain_value($column,$value) {
	return $value;
}


function display_radio_value($column,$value) {
	$list = $this->data_view[$column]['list'];
	$result = $list[$value];
	return $result;
}

function display_select_value($column,$value) {
	$list = $this->data_view[$column]['value'];
	$result = $list[$value];
	return $result;
}

function display_select_text_value($column,$value) {
	$list = $this->data_view[$column]['value'];
	$result = $list[$value];
	return $result;
}

function display_text_value($column,$value) {
	if($this->data_view[$column]['reference']) {
		$result = $this->get_value_from_table($value,$this->data_view[$column]['reference']);
	} else {
		$result = $value;
	}
	return $result;
}

function display_textarea_value($column,$value) {
	return $value;
}

function display_url_value($column,$value) {
	if($this->data_view[$column]['reference']) {
		$result = $this->get_value_from_table($value,$this->data_view[$column]['reference']);
	} elseif($value) {
		$result = "<a href=\"$value\">$value</a>";
	} else {
		$value = '';
	}
	return $result;
}


/* unify rows in table. 
 * this kind SQL's 'select distinct on'.
 * but this is also able to packing un unified columns in one row.  
 * $distinct_on must be listed all unify column name.
 * ex) $distinct_on = array('id','title','url');
 * $use_all to use packing  un unified columns in "pack name".
 * ex) $use_all = array('category'=>array('category_id','category_name'));
 * In this case, pack name is 'category'.
 */
function make_unified_rows($list_table,$distinct_on,$use_all) {
	$seq = -1;
	foreach($list_table as $line) {
		if($current_id!=$line['id']) {
			$current_id = $line['id'];
			$seq++;
			foreach($distinct_on as $item) {
				$unified[$seq][$item] = $line[$item];
			}
			$pack = 0;
		}
		if(count($use_all)>0) {
			foreach($use_all as $pack_name=>$pack_members) {
				foreach($pack_members as $item) {
					$unified[$seq][$pack_name][$pack][$item] = $line[$item];
				}
			}
			$pack++;
		}
	}
	return $unified;
}
} // end of class DataSet
// }}}
?>