class MyDAO {
	
	var $idField;
	var $tableName;
	var $dirty = false;
	var $objectType;
	var $fields;
	
	var $hasManyRelations = array ( );
	var $belongsToRelationships = array ( );
	var $throughRelationships = array ( );
	//var $throughRelationships = array('product'=>array('throughClass'=>'orderline', 'endClass'=>'product', 'throughField'=>'productID', 'endClassField'=>'productID'));
	

	var $joins;
	
	function throughHelper($dataObject,$requestedObject = false,$requestedClassName) {
		
		ModelBroker::loadModel ( $this->throughRelationships [$requestedClassName] ['throughClass'] );
		ModelBroker::loadModel ( $this->throughRelationships [$requestedClassName] ['endClass'] );
		
		$throughDao = DAOBroker::getDAO ( $this->throughRelationships [$requestedClassName] ['throughClass'] );
		$endDao = DAOBroker::getDAO ( $this->throughRelationships [$requestedClassName] ['endClass'] );
		
		if ($this->throughRelationships [$requestedClassName] ['throughField'] == 'id') {
			$this->throughRelationships [$requestedClassName] ['throughField'] = $throughDao->getIDField ();
		}
		
		if ($this->throughRelationships [$requestedClassName] ['endField'] == 'id') {
			$this->throughRelationships [$requestedClassName] ['endField'] = $endDao->getIDField ();
		}
		
		$sql = "SELECT end.* FROM " . $endDao->tableName . ' as end, ' . $throughDao->tableName . ' as through  WHERE ';
		$sql .= ' through.' . $throughDao->renameFieldsInQuery ( $this->throughRelationships [$requestedClassName] ['throughField'] ) . ' = ';
		$sql .= ' end.' . $endDao->renameFieldsInQuery ( $this->throughRelationships [$requestedClassName] ['endField'] );
		$sql .= ' AND through.' . $throughDao->renameFieldsInQuery ( $this->throughRelationships [$requestedClassName] ['throughKnownField'] ) . ' = ' . $dataObject->id . ' ';
		
		if ($requestedObject) {
			$sql .= $this->buildWhereFromExpert ( $expert );
			$sql .= $this->buildLimitOrderAndGroupFromExpert ( $dataObject );
		}
		
		return $endDao->queryObjectList ( 0, 0, $sql );
	}
	
	function hasManyHelper($dataObjectParent,$dataObject,$className) {
		
		ModelBroker::loadModel ( $className );
		
		if (! $dataObject) {
			$dataObject = new $className ( );
		}
		
		$dao = DAOBroker::getDAO ( $className );
		
		$setMethod = 'set' . ucfirst ( $this->hasManyRelations [$className] ['fk'] );
		$dataObject->$setMethod ( $dataObjectParent->id );
		
		return $dao->search ( $dataObject );
	}
	
	function belongsToHelper($dataObject,$className) {
		ModelBroker::loadModel ( $className );
		$getMethod = 'get' . ucfirst ( $this->belongsToRelationships [$className] ['methodName'] );
		$dao = DAOBroker::getDAO ( $className );
		return $dao->load ( $dataObject->$getMethod () );
	}
	
	function clean($in) {
		if (is_numeric ( $in )) {
			return $in;
		} else {
			return "'" . mysql_escape_string ( $in ) . "'";
		}
	}
	
	function getIDField() {
		return $this->idField;
	}
	
	function mysqlDateToCMSDate($value) {
		$parts = explode ( '-', $value );
		
		//var_dump($parts);
		return $parts [1] . '-' . $parts [2] . '-' . $parts [0];
	}
	
	function cleanArray($array) {
		if (! is_array ( $array )) {
			return $array;
		}
		
		foreach ( $array as $k => $v ) {
			$key = array_search ( $k, $this->fields );
			
			if (strpos ( $k, 'Date' ) === false) {
				$array [$key] = stripslashes ( $array [$k] );
			} else {
				$array [$key] = $this->mysqlDateToCMSDate ( $array [$k] );
			}
			
			if ($k == $this->idField) {
				$array ['id'] = $array [$this->idField];
				unset ( $array [$this->idField] );
			}
		}
		
		return $array;
	}
	
	function sendToPostArray() {
		foreach ( $this->fields as $k => $v ) {
			$_POST [$k] = $v;
		}
		$_POST [$this->idField] = $this->id;
	}
	
	function loadRandom($debug = false) {
		$sql = "Select * from $this->tableName order by rand() limit 1";
		
		if ($debug) {
			echo "<br><b>Load Random Query:</b> $sql <br>";
		} else {
			$result = mysqlQueryWrapper ( $sql );
			if (mysql_num_rows ( $result ) == 0) {
				return false;
			}
			
			if (mysql_error ()) {
				die ( mysql_error () );
			} else {
				$expert = new $this->objectType ( );
				$this->populateFromArray ( mysql_fetch_assoc ( $result ), true, $expert );
				return $expert;
			}
		}
	}
	
	function renameFieldsInQuery($sql) {
		$keys = array_keys ( $this->fields );
		$values = array_values ( $this->fields );
		
		return str_replace ( $keys, $values, $sql );
	}
	
	function load($id,$debug = false,$sql = '') {
		if ($sql == '') {
			$sql = "Select * from $this->tableName where " . $this->additionalWhereClause () . " AND $this->idField = $id limit 1";
		}
		
		if ($debug) {
			echo "<br><b>Load Query:</b> $sql <br>";
		} else {
			$result = mysqlQueryWrapper ( $sql );
			if (mysql_num_rows ( $result ) == 0) {
				return false;
			}
			
			if (mysql_error ()) {
				die ( mysql_error () );
			} else {
				$expert = new $this->objectType ( );
				$expert->populateFromArray ( $this->cleanArray ( mysql_fetch_assoc ( $result ) ), true, $expert );
				return $expert;
			}
		}
	}
	
	function implodeKeys($glue,$array) {
		$s = '';
		foreach ( $array as $k => $v ) {
			$s .= $k . $glue;
		}
		
		return substr ( $s, 0, (strlen ( $glue ) * - 1) );
	}
	
	function americanToMysqlDate($value) {
		$parts = explode ( '-', $value );
		return $parts [2] . '-' . $parts [0] . '-' . $parts [1];
	}
	
	function loadMultipleIDs($ids,$keys = false,$debug = false) {
		if (! is_array ( $ids )) {
			die ( 'load multiple - ids not array' );
		}
		
		$string = '';
		if ($keys) {
			$string = "'" . $this->implodeKeys ( "','", $ids ) . "'";
		} else {
			$string = "'" . implode ( "','", $ids ) . "'";
		}
		
		$sql = 'Select * FROM ' . $this->tableName . ' WHERE ' . $this->additionalWhereClause () . ' AND ' . $this->idField . ' in (' . $string . ')';
		
		if ($debug) {
			echo "<br><b>Load Query:</b> $sql <br>";
		} else {
			return $this->queryObjectList ( false, false, $sql );
		}
	}
	
	function create(&$expert,$debug = false) {
		$sql = "insert into $this->tableName ";
		$fields = "";
		$values = "";
		
		foreach ( $expert->fields as $k => $v ) {
			$method = 'get' . ucfirst ( $v );
			$k = $v;
			$v = $expert->$method ();
			if (isset ( $this->fields [$k] )) {
				$k = $this->fields [$k];
				
				if (($v != '') || ($k == 'created')) {
					$fields .= "`" . $k . "`,";
					if ($k == 'created') {
						$values .= "now(),";
					} else if (strpos ( strtolower ( $k ), '_date' ) !== false) {
						$values .= "'" . $this->americanToMysqlDate ( $v ) . "',";
					} else if (strpos ( strtolower ( $k ), 'Date' ) !== false) {
						$values .= "'" . $this->americanToMysqlDate ( $v ) . "',";
					} else {
						$values .= $this->clean ( $v ) . ",";
					}
				}
			}
		}
		
		$fields = " (" . substr ( $fields, 0, - 1 ) . ") ";
		$values = " values (" . substr ( $values, 0, - 1 ) . ")";
		
		$sql .= $fields;
		$sql .= $values;
		
		if ($debug) {
			echo "<BR><b>Create Query</b>: $sql <BR>";
			exit ();
		} else {
			
			mysqlQueryWrapper ( $sql );
			if (mysql_error ()) {
				if (strpos ( mysql_error (), 'Duplicate' ) !== false) {
					return false;
				} else {
					die ( mysql_error () . '<br>' . $sql );
				}
			} else {
				$expert->id = mysql_insert_id ();
				$expert->dirty = false;
			}
		}
		
		return true;
	
	}
	
	function whereClause($expert) {
		return " WHERE " . $this->additionalWhereClause () . " AND $this->idField = " . $expert->getID () . " limit 1";
	}
	
	function matchWhereClause($expert) {
		return " WHERE " . $this->additionalWhereClause () . " ";
	
	}
	
	function additionalWhereClause() {
		return ' 1 ';
	}
	
	function delete($expert,$debug = false) {
		if ($expert->getID () == '') {
			return false;
		}
		
		$sql = "Delete from $this->tableName " . $this->whereClause ( $expert );
		
		if ($debug) {
			echo "<BR><b>Delete Query</b>: $sql <BR>";
		} else {
			
			$result = mysqlQueryWrapper ( $sql );
			if (mysql_error ()) {
				die ( mysql_error () );
			}
		}
	}
	
	function numObjects() {
		$sql = "Select count(*) from $this->tableName";
		$result = mysqlQueryWrapper ( $sql );
		return mysql_result ( $result, 0 );
	}
	
	function findAll($desc = true) {
		$desc = ($desc == true) ? 'desc' : '';
		$sql = "Select * from $this->tableName where " . $this->additionalWhereClause () . " order by $this->idField " . $desc;
		
		$result = mysqlQueryWrapper ( $sql );
		if (! mysql_error ()) {
			return new ObjectCollection ( $result, $this->objectType );
		}
	
	}
	
	function queryObjectList($limitStart = 0,$limitNum = 30,$sql = "") {
		// meant to be called statically
		if ($sql == "") {
			$sql = "Select * from $this->tableName order by $this->idField desc limit $limitStart, $limitNum";
		}
		
		$result = mysqlQueryWrapper ( $sql );
		if (! mysql_error ()) {
			return new ObjectCollection ( $result, $this->objectType );
		} else {
			die ( mysql_error () . '  -- ' . $sql );
		}
	
	}
	
	function findDistinct($col,$dataObject = null) {
		$list = array ( );
		$sql = "Select distinct(" . $this->fields [$col] . ") as list from " . $this->tableName;
		if ($dataObject != null) {
			$sql .= ' ' . $this->createMatchWhere ( $dataObject );
		}
		$query = mysqlQueryWrapper ( $sql );
		if (mysql_num_rows ( $query ) > 0) {
			while ( $row = mysql_fetch_assoc ( $query ) ) {
				$list [] = $row ['list'];
			}
		}
		return $list;
	}
	
	function createMatchWhere($expert,$start = 0,$num = 0,$debug = false) {
		$values = '';
		$sql = '';
		if ($expert->getDirty ()) {
			foreach ( $expert->getDirty () as $k ) {
				
				$v = 'get' . ucfirst ( $k );
				$v = $expert->$v ();
				
				if ($v != 'None') {
					
					$k = $this->fields [$k];
					
					if (strpos ( strtolower ( $k ), 'Date' ) !== false) {
						$values .= "`" . $k . "` = '" . $this->americanToMysqlDate ( $v ) . "' AND";
					} else if (strpos ( strtolower ( $k ), '_date' ) !== false) {
						$values .= "`" . $k . "` = '" . $this->americanToMysqlDate ( $v ) . "' AND";
					} else if ($k != 'created') {
						$values .= "`" . $k . "` = " . $this->clean ( $v ) . " AND";
					}
				}
			}
			
			$values = substr ( $values, 0, - 3 );
		}
		
		$sql .= $this->matchWhereClause ( $expert ) . ' ';
		
		if ($values != '') {
			$sql .= 'AND ' . $values;
		}
		
		if ($expert->id != - 1 && $expert->id != '') {
			$sql .= ' AND ' . $this->idField . ' = ' . $expert->id;
		}
		
		if ($start > 0) {
			$sql .= " LIMIT $start";
		}
		
		if ($num > 0) {
			$sql .= ", $num";
		}
		return $sql;
	}
	
	function buildLimitOrderAndGroupFromExpert($expert,$objectType,$count) {
		
		$start = $expert->getStart ();
		$num = $expert->getNum ();
		$orderBy = $expert->getOrderBy ();
		$asc = $expert->getAsc ();
		$groupBy = $expert->getGroupBy ();
		
		$sql = ' ';
		if ($groupBy != false) {
			
			if ($groupBy == 'id') {
				$groupBy = $this->idField;
			}
			
			if (isset ( $this->fields [$groupBy] )) {
				$sql .= ' GROUP BY ' . $this->fields [$groupBy] . ' ';
			} else {
				$sql .= ' GROUP BY ' . $groupBy . ' ';
			}
		}
		
		if ($orderBy != false) {
			
			if ($orderBy == 'id') {
				$orderBy = $this->idField;
			}
			
			if (isset ( $this->fields [$orderBy] )) {
				$sql .= ' ORDER BY ' . $this->fields [$orderBy] . ' ';
			} else {
				$sql .= ' ORDER BY ' . $orderBy . ' ';
			}
			if ($asc != false) {
				$sql .= ' ' . $asc . ' ';
			}
		}
		
		if (! $count) {
			if ($num > 0) {
				if ($start == "") {
					$start = 0;
				}
				
				$sql .= " LIMIT $start";
				$sql .= ", $num";
			}
		}
		return $sql;
	}
	
	function buildWhereFromExpert($expert,$tableName = '') {
		$sql = '';
		$values = "";
		
		if ($tableName != '') {
			$tableName = '`' . $tableName . '`.';
		}
		
		if (is_array ( $expert->getDirty () )) {
			foreach ( $expert->getDirty () as $k ) {
				
				$v = 'get' . ucfirst ( $k );
				$v = $expert->$v ();
				
				$k = $this->fields [$k];
				
				if (strpos ( strtolower ( $k ), '_date' ) !== false) {
					if (is_object ( $v )) {
						$v->fieldIsDate ();
						$values .= $tableName . "`left_table`.`" . $k . "`  " . $v->getSQL () . " AND ";
					}
				
				} else if (strpos ( strtolower ( $k ), 'Date' ) !== false) {
					$v->fieldIsDate ();
					
					$values .= $tableName . "`left_table`.`" . $k . "`  " . $v->getSql () . " AND ";
				
				} else if (is_object ( $v ) && is_subclass_of ( $v, 'JoinBuilder' )) {
					$this->joins [$k] = $v->getSQL ();
				} else if (is_object ( $v ) && is_subclass_of ( $v, 'QueryBuilder' )) {
					$values .= $tableName . "`left_table`.`" . $k . "`  " . $v->getSQL () . " AND ";
				} else if ($k != 'created') {
					$values .= $tableName . "`left_table`.`" . $k . "` = " . $this->clean ( $v ) . " AND ";
				}
			}
		}
		
		$values = substr ( $values, 0, - 4 );
		
		$sql .= $this->matchWhereClause ( $expert ) . ' ';
		
		if ($values != '') {
			$sql .= 'AND ' . $values;
		}
		
		if (is_object ( $expert->id ) && is_subclass_of ( $expert->id, 'QueryBuilder' )) {
			$sql .= ' AND ' . $this->idField . ' ' . $expert->id->getSQL ();
		
		} else if ($expert->id != - 1 && $expert->id != '') {
			$sql .= ' AND ' . $this->idField . ' = ' . $expert->id;
		}
		
		return $sql;
	}
	
	function search($expert,$debug = false) {
		return $this->searchHelperNew ( $expert, $debug, false );
	}
	
	function searchCount($expert,$debug = false) {
		
		return $this->searchHelperNew ( $expert, $debug, true );
	}
	
	function searchHelperNew($expert,$debug = false,$count = false) {
		
		$sql = "SELECT left_table.* ";
		
		$from = " from $this->tableName  left_table";
		
		$where = $this->buildWhereFromExpert ( $expert );
		$joins = array ( );
		$extras = array ( );
		
		$counter = 0;
		
		$objectTypes = array ( );
		
		$objectTypes [] = $this->objectType;
		if (is_array ( $this->joins )) {
			
			foreach ( $this->joins as $k => $v ) {
				
				$joins [$counter] = ' ' . $v ['join'] . ' ';
				$joins [$counter] = str_replace ( array ('LEFT_KEY', 'RIGHT_TABLE' ), array ($k, 'RIGHT_TABLE_' . $counter ), $joins [$counter] );
				foreach ( $v ['fields'] as $fieldName ) {
					$extras [] = '`RIGHT_TABLE_' . $counter . '`.`' . $fieldName . '` as ' . $counter . '_' . $fieldName . ' ';
				}
				
				if (isset ( $v ['objType'] )) {
					$objectTypes [] = $v ['objType'];
					$tmpDao = DAOBroker::getDAO ( $v ['objType'] );
					$extras [] = '`RIGHT_TABLE_' . $counter . '`.`' . $tmpDao->idField . '` as ' . $counter . '_' . $tmpDao->idField . ' ';
				
				}
				
				$counter ++;
			}
		}
		if (count ( $extras ) > 0) {
			$extraFields = implode ( ',', $extras );
			$sql .= ', ' . $extraFields;
		}
		
		if ($count) {
			$sql = "SELECT count(*) ";
		}
		
		$joined = implode ( '  ', $joins );
		
		$sql .= $from;
		$sql .= $joined;
		$sql .= $where;
		
		// limit
		$sql .= $this->buildLimitOrderAndGroupFromExpert ( $expert, $this->objectType, $count );
		
		//di($this->joins);
		

		if (! $count) {
			if ($debug) {
				echo "<BR><b>Match Query</b>: $sql <BR>";
				exit ();
			} else {
				$result = mysqlQueryWrapper ( $sql );
				if (! mysql_error ()) {
					
					return new ObjectCollection ( $result, $objectTypes );
				} else {
					die ( mysql_error () . '  -- ' . $sql );
				}
			}
		} else {
			
			if ($debug) {
				echo "<BR><b>Search Count Query</b>: $sql <BR>";
				exit ();
			} else {
				$result = mysqlQueryWrapper ( $sql );
				if (! mysql_error ()) {
					return mysql_result ( $result, 0 );
				}
			}
		}
	}
	
	function update($expert,$debug = false) {
		
		$sql = "update $this->tableName set ";
		$values = "";
		
		foreach ( $expert->getDirty () as $k ) {
			
			$v = 'get' . ucfirst ( $k );
			$v = $expert->$v ();
			
			$k = $this->fields [$k];
			
			if (strpos ( strtolower ( $k ), '_date' ) !== false) {
				$values .= "`" . $k . "` = '" . $this->americanToMysqlDate ( $v ) . "',";
			} else if (strpos ( strtolower ( $k ), 'Date' ) !== false) {
				$values .= "`" . $k . "` = '" . $this->americanToMysqlDate ( $v ) . "',";
			} else if ($k != 'created') {
				$values .= "`" . $k . "` = " . $this->clean ( $v ) . ",";
			}
		
		}
		
		$values = substr ( $values, 0, - 1 );
		
		$sql .= $values;
		
		$sql .= $this->whereClause ( $expert );
		
		if ($debug) {
			echo "<BR><b>Update Query</b>: $sql <BR>";
			exit ();
		} else {
			
			mysqlQueryWrapper ( $sql );
			if (mysql_error ()) {
				die ( mysql_error () );
			} else {
				$expert->dirty = false;
			}
		}
	}
}