<?php

/***************************************************************

*  Copyright notice

*  (c) 2009, jianyuzhu@gmail.com

*  All rights reserved

*  This script is part of the PPEMI project.

***************************************************************/



//

include_once(CONFIG_PATH . CONFIG_DIR_CLASSES . 'base.item.class.php');



class BaseItemItemsBase extends BaseItemBase {

	var $join_key = 'id';

	var $relation_key = 'id';

	

	//join

	var $table_join = '';

	var $table_join_key = '';

	var $table_join_params = '';

	var $table_join_fields = array();

	

	//relation

	var $table_relation = '';

	var $table_relation_key = '';

	var $table_relation_params = '';

	var $table_relation_fields = array();

	

	//joins

	//	table, key, fkey, params, fields

	//

	var $joins = array();

	

	//relations

	//	table, key, fkey, params, fields

	//		params

	//		2	=

	//		3	key

	//

	var $relations = array();

	

	//constructor

	function BaseItemItemsBase() {

		parent::BaseItemBase();

	}

	

	//methods

	function getItems($filter = '', $check = 0, $limit = true) {

		$query_field_str = '*';

		if( $this->list_fields == 'ALL' ) {

			$query_field_str = "*";

		} elseif( $this->list_fields == '*' ) {

			$query_field_str = "a.*";

		} elseif( is_array($this->list_fields) && sizeof($this->list_fields) > 0 ) {

			$query_field_str = implode(', a.', $this->list_fields);

		}

		

		$query_str = "";

		$query_str .= $this->_get_query_str($filter, $check);

		

		$query_order_str = "";

		if( $this->sort_order == true ) {

			if( is_array($this->sort_fields) ) {

				$query_order_str = " order by ";

				$i = 0;

				foreach($this->sort_fields as $k => $v) {

					if( substr($v, 0, 1) == '=' ) {

						$v = substr($v, 1);

					} else {

						$v = $this->prefix . $v;

					}

					if( substr($v, -1) == '-' ) {

						$as = 'desc';

						$v = substr($v, 0, -1);

					} elseif( substr($v, -1) == '+' ) {

						$as = 'asc';

						$v = substr($v, 0, -1);

					} else {

						$as = 'asc';

					}

					if( $i > 0 ) {

						$query_order_str .= ", ";

					}

					$query_order_str .= " a." . $v . " " . $as;

					$i++;

				}

			} else {

				$query_order_str = " order by a." . $this->prefix . "sort_order asc";

			}

		}

		

		$query_limit_str = "";

		if( $limit == true ) {

			$query_limit_str = $this->getQueryLimitStr();

		}

		

		//

		$query = "select " . $query_field_str . " ";

		if( $this->table_join != '' ) {

			if( $this->table_join_fields == '*' ) {

				$query .= ", b.*";

			} elseif( is_array($this->table_join_fields) ) {

				foreach($this->table_join_fields as $k => $v) {

					$query .= ", b." . $v;

				}

			}

		}

		if( $this->table_relation != '' ) {

			if( $this->table_relation_fields == '*' ) {

				$query .= ", c.*";

			} elseif( is_array($this->table_relation_fields) ) {

				foreach($this->table_relation_fields as $k => $v) {

					$query .= ", c." . $v;

				}

			}

		}

		if( is_array($this->joins) ) {

			foreach($this->joins as $jk => $join) {

				if( isset($join['fields']) ) {

					if( $join['fields'] == '*' ) {

						$query .= ", tj.*";

					} elseif( is_array($join['fields']) ) {

						foreach($join['fields'] as $k => $v) {

							$query .= ", tj" . $jk . "." . $v;

						}

					}

				}

			}

		}

		if( is_array($this->relations) ) {

			foreach($this->relations as $rk => $relation) {

				if( isset($relation['fields']) ) {

					if( $relation['fields'] == '*' ) {

						$query .= ", tr.*";

					} elseif( is_array($relation['fields']) ) {

						foreach($relation['fields'] as $k => $v) {

							$query .= ", tr" . $rk . "." . $v;

						}

					}

				}

			}

		}

		//

		$query .= " from " . $this->table . " a ";

		if( $this->table_join != '' ) {

			$query .= " left join " . $this->table_join . " b " . " on a." . $this->join_key . " = b." . $this->table_join_key . " ";

			if( is_array($this->table_join_params) ) {

				foreach($this->table_join_params as $k => $v) {

					$query_str .= " and b." . $k . " = '" . $v . "'";

				}

			}

		}

		if( is_array($this->joins) ) {

			foreach($this->joins as $jk => $join) {

				$query .= " left join " . $join['table'] . " tj" . $jk . " on a." . $this->prefix . $join['fkey'] . " = tj" . $jk . "." . $join['key'] . " ";

				if( $join['params'] != '' ) {

					if( is_array($join['params']) ) {

						foreach($join['params'] as $k => $v) {

							$query .= " and tj" . $jk . "." . $k . " = '" . $v . "' ";

						}

					}

				}

			}

		}

		if( $this->table_relation != '' ) {

			$query .= ", " . $this->table_relation . " c ";

		}

		if( is_array($this->relations) ) {

			foreach($this->relations as $rk => $relation) {

				$query .= ", " . $relation['table'] . " tr" . $rk . " ";

			}

		}

		$query .= " where " . $this->_query_owner_rt('a') 

					. $query_str . $query_order_str . $query_limit_str;

		$rows = $this->adb->getRows($query);

		

		//

		if( $this->format == true ) {

			for($i=0, $n=sizeof($rows); $i<$n; $i++) {

				$rows[$i] = $this->_post_field($rows[$i], '', $this->prefix, $this->format_check);

			}

		}

		

		return $rows;

	}

	

	function getItemsTotal($filter = '', $check = 0) {

		$query_str = "";

		$query_str .= $this->_get_query_str($filter, $check);

		

		//

		$query = "select distinct a." . $this->prefix . "id from " . $this->table . " a ";

		if( $this->table_join != '' ) {

			$query .= " left join " . $this->table_join . " b " . " on a." . $this->join_key . " = b." . $this->table_join_key . " ";

			if( is_array($this->table_join_params) ) {

				foreach($this->table_join_params as $k => $v) {

					$query_str .= " and b." . $k . " = '" . $v . "'";

				}

			}

		}

		if( is_array($this->joins) ) {

			foreach($this->joins as $jk => $join) {

				$query .= " left join " . $join['table'] . " tj" . $jk . " on a." . $this->prefix . $join['fkey'] . " = tj" . $jk . "." . $join['key'] . " ";

				if( $join['params'] != '' ) {

					if( is_array($join['params']) ) {

						foreach($join['params'] as $k => $v) {

							$query .= " and tj" . $jk . "." . $k . " = '" . $v . "' ";

						}

					}

				}

			}

		}

		if( $this->table_relation != '' ) {

			$query .= ", " . $this->table_relation . " c ";

		}

		if( is_array($this->relations) ) {

			foreach($this->relations as $rk => $relation) {

				$query .= ", " . $relation['table'] . " tr" . $rk . " ";

			}

		}

		$query .= " where " . $this->_query_owner_rt('a') 

					. $query_str;

		

		return $this->adb->getCount($query);

	}

	

	//private

	function _get_query_str($filter = '', $check = 0) {

		$query_str = "";

		if( $check == -1 ) {

			$query_str .= "";

		} elseif( $this->_table_status_usable == true ) {

			$query_str .= " and a." . $this->prefix . "status = 1";

		}

		if( is_array($filter) ) {

			$filter = func_db_input($filter);

			if( is_array($this->filter_query) ) {

				$filter = array_merge($filter, $this->filter_query);

			}

			if( is_array($this->filters) ) {

				foreach($this->filters as $k => $v) {

					$filter[$k] = $v;

				}

			}

			//

			if( isset($filter['keyword']) && $this->_not_null($filter['keyword']) == true ) {

				if( is_array($this->search_fields) ) {

					if( $this->search_type != 'or' ) {

						$this->search_type = 'and';

					}

					$i = 0;

					$query_str .= " and ( ";

					foreach($this->search_fields as $k => $field) {

						if( $i > 0 ) {

							$query_str .= " " . $this->search_type . " ";

						}

						$query_str .= " a." . $this->prefix . $field . " like '%" . $filter['keyword'] . "%' ";

						$i++;

					}

					$query_str .= " ) ";

				} elseif( is_string($this->search_fields) ) {

					$query_str .= " and a." . $this->prefix . $this->search_fields . " like '%" . $filter['keyword'] . "%' ";

				}

			}

			if( isset($filter['ids']) && $this->_not_null($filter['ids']) == true ) {

				if( is_array($filter['ids']) ) {

					$query_str .= " and a." . $this->prefix . "id in (" . $this->_implode(', ', $filter['ids']) . ") ";

				} else {

					$query_str .= " and a." . $this->prefix . "id = '" . (int)$filter['ids'] . "' ";

				}

			}

			if( isset($filter['category']) ) {

				if( is_array($filter['category']) ) {

					$query_str .= " and a.categories_id in (" . $this->_implode(', ', $filter['category']) . ") ";

				} else {

					$query_str .= " and a.categories_id = '" . (int)$filter['category'] . "' ";

				}

			}

			if( is_array($this->filter_fields) && sizeof($this->filter_fields) > 0 ) {

				$qt = $this->_get_query_type();

				foreach($filter as $k => $v) {

					if( is_array($this->filter_map) && isset($this->filter_map[$k]) ) {

						$k = $this->filter_map[$k];

					}

					if( in_array($k, $this->filter_fields) ) {

						$t = '';

						$p = 'a.' . $this->prefix;

						if( strpos($k, ':') !== false ) {

							$ks = explode(':', $k);

							$k = $ks['0'];

							$t = $ks['1'];

							if( sizeof($ks) > 2 ) {

								$p = $ks['2'];

							}

						}

						if( substr($k, 0, 1) == '=' ) {

							$k = substr($k, 1);

							$p = 'a.';

						}

						if( !in_array($t, $qt) ) {

							$t = '';

						}

						if( in_array($t, array('=', '>', '>=', '<', '<=', '!=')) ) {

							if( $v === 'now()' || $v === 'now' ) {

								$query_str .= " and " . $p . $k . " " . $t . " now() ";

							} else {

								$query_str .= " and " . $p . $k . " " . $t . " '" . $v . "' ";

							}

						} elseif( in_array($t, array('[]', '[)', '(]', '()')) ) {

							if( is_array($v) ) {

								$t1 = substr($t, 0, 1);

								$t2 = substr($t, 1, 1);

								if( $t1 == '[' ) {

									$t1 = '<=';

								} elseif( $t1 == '(' ) {

									$t1 = '<';

								}

								if( $t2 == '[' ) {

									$t2 = '<=';

								} elseif( $t1 == '(' ) {

									$t2 = '<';

								}

								

								$query_str .= " and " . $p . $k . " " . $t1 . " '" . $v['0'] . "' ";

								$query_str .= " and " . $p . $k . " " . $t2 . " '" . $v['1'] . "' ";

							}

						} elseif( in_array($t, array('like', 'notlike', '%like%', '%like', 'like%',)) ) {

							if( $t == 'notlike' ) {

								$query_str .= " and " . $p . $k . " not like '%" . $v . "%' ";

							} else {

								$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";

							}

						} else {

							if( $v === 'now()' || $v === 'now' ) {

								$query_str .= " and " . $p . $k . " < now() ";

							} elseif( is_array($v) && sizeof($v) > 0 ) {

								$query_str .= " and " . $p . $k . " in ('" . implode("','", $v) . "') ";

							} elseif( is_int($v) ) {

								$query_str .= " and " . $p . $k . " = '" . $v . "' ";

							} elseif( is_numeric($v) ) {

								$query_str .= " and " . $p . $k . " = '" . $v . "' ";

							} elseif( is_string($v) ) {

								$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";

							} else {

								$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";

							}

						}

					}

				}

			}

		}

		//

		$query_str .= $this->_get_query_str_relation($filter, $check);

		//

		$query_str .= $this->_get_query_str_custom($filter, $check);

		

		return $query_str;

	}

	

	function _get_query_str_relation($filter = '', $check = 0) {

		$query_str = "";

		

		if( $this->table_relation != '' ) {

			$query_str .= " and a." . $this->relation_key . " = c." . $this->table_relation_key;

			if( is_array($this->table_relation_params) ) {

				foreach($this->table_relation_params as $k => $v) {

					$query_str .= " and c." . $k . " = '" . $v . "'";

				}

			}

		}

		if( is_array($this->relations) ) {

			$qt = $this->_get_query_type();

			foreach($this->relations as $rk => $relation) {

				$query .= " and a." . $relation['fkey'] . " = tr" . $rk . "." . $relation['key'] . " ";

				if( $relation['params'] != '' ) {

					if( is_array($relation['params']) ) {

						foreach($relation['params'] as $k => $v) {

							$t = '';

							$p = "tr" . $rk . ".";

							if( strpos($k, ':') !== false ) {

								$ks = explode(':', $k);

								$k = $ks['0'];

								$t = $ks['1'];

								if( sizeof($ks) > 2 ) {

									$p = $ks['2'];

								}

							}

							if( !in_array($t, $qt) ) {

								$t = '';

							}

							if( in_array($t, array('=', '>', '>=', '<', '<=', '!=')) ) {

								if( $v === 'now()' || $v === 'now' ) {

									$query_str .= " and " . $p . $k . " " . $t . " now() ";

								} else {

									$query_str .= " and " . $p . $k . " " . $t . " '" . $v . "' ";

								}

							} elseif( in_array($t, array('[]', '[)', '(]', '()')) ) {

								if( is_array($v) ) {

									$t1 = substr($t, 0, 1);

									$t2 = substr($t, 1, 1);

									if( $t1 == '[' ) {

										$t1 = '<=';

									} elseif( $t1 == '(' ) {

										$t1 = '<';

									}

									if( $t2 == '[' ) {

										$t2 = '<=';

									} elseif( $t1 == '(' ) {

										$t2 = '<';

									}

									

									$query_str .= " and " . $p . $k . " " . $t1 . " '" . $v['0'] . "' ";

									$query_str .= " and " . $p . $k . " " . $t2 . " '" . $v['1'] . "' ";

								}

							} elseif( in_array($t, array('like', 'notlike', '%like%', '%like', 'like%',)) ) {

								if( $t == 'notlike' ) {

									$query_str .= " and " . $p . $k . " not like '%" . $v . "%' ";

								} else {

									$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";

								}

							} else {

								if( $v === 'now()' || $v === 'now' ) {

									$query_str .= " and " . $p . $k . " < now() ";

								} elseif( is_array($v) && sizeof($v) > 0 ) {

									$query_str .= " and " . $p . $k . " in ('" . implode("','", $v) . "') ";

								} elseif( is_int($v) ) {

									$query_str .= " and " . $p . $k . " = '" . $v . "' ";

								} elseif( is_numeric($v) ) {

									$query_str .= " and " . $p . $k . " = '" . $v . "' ";

								} elseif( is_string($v) ) {

									$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";

								} else {

									$query_str .= " and " . $p . $k . " like '%" . $v . "%' ";

								}

							}

						}

					}

				}

			}

		}

		

		return $query_str;

	}

	

	function _get_query_str_custom($filter = '', $check = 0) {

		return "";

	}

}

//

?>