<?php

require_once XOOPS_TRUST_PATH . '/libs/xoonips/class/core/BeanBase.class.php';
require_once XOOPS_TRUST_PATH . '/libs/xoonips/class/core/Utils.class.php';

/**
 * @brief operate xoonips_user_field_group table
 *
 */
class Xoonips_UserFieldGroupBean extends Xoonips_BeanBase {

	/**
	 * Constructor
	 **/
	public function Xoonips_UserFieldGroupBean($dirname, $trustDirname) {
		$this->Xoonips_BeanBase($dirname, $trustDirname);
		$this->setTableName('user_field_group', false);

		$this->grouplinktable = $this->prefix('user_field_group_field_detail_link');
		$this->detailtable = $this->prefix('user_field_detail');
	}

	/**
	 * get user type groups by usertypeid
	 * @param user type id
	 * @return array
	 */
	public function getUserTypeGroups($usertypeId) {
		$sql = 'SELECT * FROM ' . $this->table . " WHERE user_type_id=$usertypeId ORDER BY group_id";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$ret = array();
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 *
	 * get default user type group list
	 *
	 * @param
	 * @return default usertype group list
	 */
	public function getDefaultUserTypeGroup() {
		$ret = array();
		$table = $this->prefix($this->modulePrefix('default_user_field_group'));
		$sql = 'SELECT * FROM ' . $table . ' ORDER BY weight';
		$result = $this->execute($sql);
		if (!$result) {
			return $ret;
		}
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 * insert new usertype group
	 *
	 * @param  $info: usertype group info
	 * @return boolean  true:success,false:failed
	 */
	public function insert($info, &$insertId) {
		$sql = "INSERT INTO $this->table (released, preselect, name, xml, weight, occurrence, update_id)";
		$sql .= ' VALUES (' . Xoonips_Utils::convertSQLNum($info['released']) . ','
			. Xoonips_Utils::convertSQLNum($info['preselect']) . ','
			. Xoonips_Utils::convertSQLStr($info['name']) . ','
			. Xoonips_Utils::convertSQLStr($info['xml']) . ','
			. Xoonips_Utils::convertSQLNum($info['weight']) . ','
			. Xoonips_Utils::convertSQLNum($info['occurrence']) . ','
			. Xoonips_Utils::convertSQLNum($info['update_id']). ')';
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$insertId = $this->getInsertId();
		return true;
	}

	/**
	 * insert default group
	 *
	 * @param  $info: default group info
	 * @return boolean  true:success,false:failed
	 */
	public function insertDefault($info, &$insertId) {
		$table = $this->prefix($this->modulePrefix('default_user_field_group'));
		$sql = "INSERT INTO $table (name, xml, weight, occurrence)";
		$sql .= ' VALUES (' . Xoonips_Utils::convertSQLStr($info['name']) . ','
			. Xoonips_Utils::convertSQLStr($info['xml']) . ','
			. Xoonips_Utils::convertSQLNum($info['weight']) . ','
			. Xoonips_Utils::convertSQLNum($info['occurrence']) . ')';
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$insertId = $this->getInsertId();
		return true;
	}

	/**
	 * get usertype group edit info
	 *
	 * @param  $usertypeId: usertype id
	 * @return user type group information
	 */
	public function getUsertypeGroupEditInfo($usertypeId) {
		$ret = array();
		$sql = "SELECT a.* FROM $this->table a LEFT JOIN $this->table b ON a.update_id=b.group_id WHERE a.user_type_id=$usertypeId ORDER BY a.weight";
		$result = $this->execute($sql);
		if (!$result) {
			return $ret;
		}
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 * update weight
	 *
	 * @param  $group_id, $weight
	 * @return boolean  true:success,false:failed
	 */
	public function updateWeight($groupId, $weight) {
		$sql = "UPDATE $this->table SET weight=$weight WHERE group_id=$groupId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * delete
	 *
	 * @param  $group_id,
	 * @return boolean  true:success,false:failed
	 */
	public function delete($groupId) {
		$sql = "DELETE FROM $this->table WHERE group_id=$groupId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}

		$sql = "DELETE FROM $this->table WHERE update_id=$groupId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}

		return true;
	}

	/**
	 * delete by usertpye_id
	 *
	 * @param  $usertypeid,
	 * @return boolean  true:success,false:failed
	 */
	public function deleteByUsertypeId($usertypeid) {
		$sql = "DELETE FROM $this->table WHERE user_type_id=$usertypeid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * update usertype group
	 *
	 * @param  $usertypeId: usertype id
	 * @return boolean  true:success,false:failed
	 */
	public function updateNewGroup($base_usertypeid, $copy_usertypeid) {
		$sql = "UPDATE $this->table SET "
			. ' released = 1 '
			. ", user_type_id = $base_usertypeid ";
		$sql .= " WHERE user_type_id=$copy_usertypeid AND released=0 AND update_id IS NULL";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * update usertype group
	 *
	 * @param  $usertypeId: usertype id
	 * @return boolean  true:success,false:failed
	 */
	public function updateCopyToBaseGroup($usertypeId) {
		$sql = "UPDATE $this->table t1, $this->table t2 SET "
			. ' t1.weight = t2.weight '
			. ', t1.name = t2.name '
			. ', t1.xml = t2.xml '
			. ', t1.occurrence = t2.occurrence ';
		$sql .= " WHERE t1.group_id=t2.update_id AND t2.user_type_id=$usertypeId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * delete usertype group
	 *
	 * @param  $usertypeId: usertype id
	 * @return boolean  true:success,false:failed
	 */
	public function deleteCopyUsertypeGroup($usertypeId) {
		$sql = "DELETE FROM $this->table WHERE user_type_id=$usertypeId AND update_id IS NOT NULL";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * check exist user_type group name
	 * @param  usertype_id, group_id, name
	 * @return boolean  true:success,false:failed
	 */
	public function existGroupName($itid, $gid, $gname) {
		$sql = 'SELECT name FROM ' . $this->table . " WHERE user_type_id=$itid AND group_id<>$gid AND name=" . Xoonips_Utils::convertSQLStr($gname);
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		while ($row = $this->fetchArray($result)) {
			return true;
		}
		return false;
	}

	/**
	 * check exist user_type group xml
	 * @param  usertype_id, group_id, xml
	 * @return boolean  true:success,false:failed
	 */
	public function existGroupXml($itid, $gid, $gxml) {
		$sql = 'SELECT xml FROM ' . $this->table . " WHERE user_type_id=$itid AND group_id<>$gid AND xml=" . Xoonips_Utils::convertSQLStr($gxml);
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		while ($row = $this->fetchArray($result)) {
			return true;
		}
		return false;
	}

	/**
	 * get max group weight
	 * @return max group weight
	 */
	public function getMaxGroupWeight() {
		$sql = 'SELECT MAX(weight) AS maxWeight FROM ' . $this->table;
		$result = $this->execute($sql);
		if (!$result) {
			return 0;
		}
		while ($row = $this->fetchArray($result)) {
			return $row['maxWeight'];
		}
		return 0;
	}

	/**
	 * get usertype group edit info
	 *
	 * @param  $gid: group id
	 * @return user type group information
	 */
	public function getGroupEditInfo($gid) {
		$sql = 'SELECT a.preselect as a_preselect, b.preselect as b_preselect, '
			. ' a.released as a_released, b.released as b_released, '
			. ' a.name as a_name, b.name as b_name, '
			. ' a.xml as a_xml, b.xml as b_xml, '
			. ' a.occurrence as a_occurrence, b.occurrence as b_occurrence, '
			. ' a.weight as a_weight, b.weight as b_weight, '
			. ' a.update_id as a_update_id, b.update_id as b_update_id, '
			. ' a.group_id as a_group_id, b.group_id as b_group_id '
			. " FROM $this->table a LEFT JOIN $this->table b ON a.update_id=b.group_id WHERE a.group_id=$gid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$row = $this->fetchArray($result);
		$this->freeRecordSet($result);
		return $row;
	}

	/**
	 * update group
	 *
	 * @param  $info: group info
	 * @return boolean  true:success,false:failed
	 */
	public function update($info, $gid) {
		$sql = "UPDATE $this->table SET "
			. '  name = ' . Xoonips_Utils::convertSQLStr($info['name'])
			. ', xml = ' . Xoonips_Utils::convertSQLStr($info['xml'])
			. ', occurrence = ' . Xoonips_Utils::convertSQLNum($info['occurrence']);
		$sql .= " WHERE group_id=$gid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	// get detail list
	public function getDetailList($usertypeid , $baseId) {
		$detailTable = $this->prefix($this->modulePrefix('user_field_detail'));
		$sql = "SELECT dt.user_detail_id, dt.name as detail_name, gt.name as group_name"
			." FROM " . $detailTable . " dt, " . $this->table . " gt "
			." WHERE dt.user_type_id=gt.user_type_id AND dt.group_id=gt.group_id "
			." AND dt.nondisplay='0' AND dt.user_detail_id<>" . $baseId
			." AND dt.user_type_id=" . $usertypeid . " ORDER BY dt.weight";
		$result = $this->execute($sql);
		$ret = array();
		if (!$result) {
			return $ret;
		}
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 * do copy by id
	 *
	 * @param  $usertypeId, $map
	 * @return boolean true:success,false:failed
	 */
	public function copyById($usertypeId, &$map, $update = false, $import = false) {

		// get copy information
		$groupObj = $this->getUserTypeGroups($usertypeId);
		if (!$groupObj) return false;

		// do copy by obj
		return $this->copyByObj($groupObj, $map, $update, $import);
	}

	/**
	 * do copy by obj
	 *
	 * @param  $groupObj, $map
	 * @return boolean true:success,false:failed
	 */
	public function copyByObj($groupObj, &$map, $update, $import) {

		// insert copy
		foreach ($groupObj as $group) {

			$group['released'] = $import ? $group['released'] : 0;
			$group['user_type_id'] = $map['usertype'][$group['user_type_id']];
			$group['update_id'] = $update ? $group['group_id'] : NULL;

			$insertId = null;
			if (!$this->insert($group, $insertId)) return false;

			$map['group'][$group['group_id']] = $insertId;
		}
		return true;
	}

	// get countUsergroups
	public function countUsergroups() {
		$sql = 'SELECT group_id FROM ' . $this->table . ' WHERE update_id IS NULL';
		$result = $this->execute($sql);
		$ret = 0;
		if (!$result) {
			return $ret;
		}
		$ret = $this->getRowsNum($result);
		$this->freeRecordSet($result);
		return $ret;
	}

	// getUsergrouplist
	public function getUsergrouplist($limit = 0, $start = 0) {
		$ret = array();
		$sql = 'SELECT at.*, bt.update_id as upid FROM ' . $this->table . " at LEFT JOIN " . $this->table . " bt"
		. " ON at.group_id=bt.update_id WHERE at.update_id IS NULL "
		. ' LIMIT ' . (int) $start . ', ' . (int) $limit;

		$result = $this->execute($sql);
		if (!$result) {
			return $ret;
		}
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 * check exist user_type group name
	 *
	 * @param  group_id, name, base_gid
	 * @return boolean  true:success,false:failed
	 */
	public function existGroupNameNG($gid, $gname, $base_gid=0) {
		$sql = 'SELECT name FROM ' . $this->table . " WHERE group_id<>$gid AND group_id<>$base_gid"
		. " AND name=" . Xoonips_Utils::convertSQLStr($gname);
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		while ($row = $this->fetchArray($result)) {
			return true;
		}
		return false;
	}

	/**
	 * check exist user_type group xml
	 *
	 * @param  group_id, xml, base_gid
	 * @return boolean  true:success,false:failed
	 */
	public function existGroupXmlNG($gid, $gxml, $base_gid=0) {
		$sql = 'SELECT xml FROM ' . $this->table . " WHERE group_id<>$gid AND group_id<>$base_gid"
		." AND xml=" . Xoonips_Utils::convertSQLStr($gxml);
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		while ($row = $this->fetchArray($result)) {
			return true;
		}
		return false;
	}

	/**
	 * do copy by id
	 *
	 * @param  $usertypeId, $map
	 * @return boolean true:success,false:failed
	 */
	public function copyByIdNG($usergroupId, &$map, $update = false, $import = false) {

		// get copy information
		$groupObj = $this->getUserGroup($usergroupId);
		if (!$groupObj) return false;

		// do copy by obj
		return $this->copyByObjNG($groupObj, $map, $update, $import);
	}

	/**
	 * do copy by obj
	 *
	 * @param  $groupObj, $map
	 * @return boolean true:success,false:failed
	 */
	public function copyByObjNG($groupObj, &$map, $update, $import) {

		// insert copy
		foreach ($groupObj as $group) {

			$group['released'] = $import ? $group['released'] : 0;
			$group['update_id'] = $update ? $group['group_id'] : NULL;

			$insertId = null;
			if (!$this->insert($group, $insertId)) return false;

			$map['group'][$group['group_id']] = $insertId;
		}
		return true;
	}

	/**
	 * get user type groups by usertypeid
	 *
	 * @param user type id
	 * @return array
	 */
	public function getUserGroup($usergroupId) {
		$sql = 'SELECT * FROM ' . $this->table . " WHERE group_id=$usergroupId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$ret = array();
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 * get usertype group edit info
	 *
	 * @param  $gid: group id (released)
	 * @return user type group information
	 */
	public function getGroupEditInfoNG($gid) {
		$sql = 'SELECT a.preselect as a_preselect, b.preselect as b_preselect, '
			. ' a.released as a_released, b.released as b_released, '
			. ' a.name as a_name, b.name as b_name, '
			. ' a.xml as a_xml, b.xml as b_xml, '
			. ' a.occurrence as a_occurrence, b.occurrence as b_occurrence, '
			. ' a.weight as a_weight, b.weight as b_weight, '
			. ' a.update_id as a_update_id, b.update_id as b_update_id, '
			. ' a.group_id as a_group_id, b.group_id as b_group_id '
			. " FROM $this->table a LEFT JOIN $this->table b ON a.group_id=b.update_id WHERE a.group_id=$gid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$row = $this->fetchArray($result);
		$this->freeRecordSet($result);
		return $row;
	}

	/**
	 * release
	 *
	 * @param  $group_id, $base_groupid
	 * @return boolean  true:success,false:failed
	 */
	public function release($group_id, $base_groupid) {
		if ($group_id == $base_groupid) {
			$sql = "UPDATE $this->table SET released = 1 WHERE group_id=$group_id";
		} else {
			$sql = "UPDATE $this->table t1, $this->table t2 SET "
			. ' t1.weight = t2.weight '
			. ', t1.name = t2.name '
			. ', t1.xml = t2.xml '
			. ', t1.occurrence = t2.occurrence ';
			$sql .= " WHERE t1.group_id=t2.update_id AND t2.group_id=$group_id";
		}
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}

		// release detail link
		if (!self::updateLinkSync($base_groupid, true)) return false;

		return true;
	}

	/**
	 *
	 * get group detail list
	 *
	 * @param $groupId
	 * @return  usertype detail list
	 */
	public function getGroupDetailsNG($groupId) {
		$ret = array();
		$sql = 'SELECT d.user_detail_id,d.name,d.xml,g.weight,g.edit_weight'
		. ' ,g.edit,g.released as link_release FROM ' . $this->detailtable . ' AS d'
		. ' LEFT JOIN ' . $this->grouplinktable . ' AS g ON d.user_detail_id=g.user_detail_id'
		. " WHERE g.group_id=$groupId ORDER BY g.edit_weight";
		$result = $this->execute($sql);
		if (!$result) {
			return $ret;
		}
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

	/**
	 * insert group link
	 *
	 * @param  $info: default detail info
	 * @return boolean  true:success,false:failed
	 */
	public function insertLink($info, &$insertId) {
		$sql = "INSERT INTO $this->grouplinktable (group_id, user_detail_id, weight, edit, edit_weight, released)"
			. ' VALUES (' . Xoonips_Utils::convertSQLStr($info['group_id']) . ','
			. Xoonips_Utils::convertSQLStr($info['user_detail_id']) . ','
			. Xoonips_Utils::convertSQLNum($info['weight']) . ','
			. Xoonips_Utils::convertSQLNum($info['edit']) . ','
			. Xoonips_Utils::convertSQLNum($info['edit_weight']) . ','
			. Xoonips_Utils::convertSQLNum($info['released']) . ')';
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		$insertId = $this->getInsertId();
		return true;
	}

	/**
	 * update group link weight
	 *
	 * @param  $groupid, $detail_id, $weight
	 * @return boolean  true:success,false:failed
	 */
	public function updateWeightNG($groupid, $detailId, $weight) {
		$sql = "UPDATE $this->grouplinktable SET edit_weight=$weight"
		. " WHERE group_id=$groupid AND user_detail_id=$detailId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * update group link released
	 *
	 * @param  $groupid, $release
	 * @return boolean  true:success,false:failed
	 */
	public function updateLinkRelease($groupid, $release=0) {
		$sql = "UPDATE $this->grouplinktable SET released=$release"
		. " WHERE group_id=$groupid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * update group link edit
	 *
	 * @param  $groupid, $detail_id, $edit
	 * @return boolean  true:success,false:failed
	 */
	public function updateLinkEdit($groupid, $detailId, $edit=0) {
		$sql = "UPDATE $this->grouplinktable SET edit=$edit"
		. " WHERE group_id=$groupid AND user_detail_id=$detailId";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * update group link sync
	 *
	 * @param  $groupid, $release
	 * @return boolean  true:success,false:failed
	 */
	public function updateLinkSync($groupid, $release=false) {
		$sql2 = 'edit=released,edit_weight=weight';
		if ($release) {
			$sql2 = 'released=edit,weight=edit_weight';
		}
		$sql = "UPDATE $this->grouplinktable SET $sql2"
		. " WHERE group_id=$groupid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 * delete group link
	 *
	 * @param  $groupid
	 * @return boolean  true:success,false:failed
	 */
	public function deleteLink($groupid) {
		$sql = "DELETE FROM $this->grouplinktable WHERE group_id=$groupid";
		$result = $this->execute($sql);
		if (!$result) {
			return false;
		}
		return true;
	}

	/**
	 *
	 * get group by detail id
	 *
	 * @param $groupId
	 * @return  usertype detail list
	 */
	public function getGroupByDetailId($detailid) {
		$ret = array();
		$sql = "SELECT g.* FROM $this->table AS g"
		. " LEFT JOIN $this->grouplinktable AS l ON g.group_id=l.group_id"
		. " WHERE l.user_detail_id=$detailid AND l.edit=1";
		$result = $this->execute($sql);
		if (!$result) {
			return $ret;
		}
		while ($row = $this->fetchArray($result)) {
			$ret[] = $row;
		}
		$this->freeRecordSet($result);
		return $ret;
	}

  public function getDetailIdbyXml($xml) {
    $ret = array();
    $sql = "select user_detail_id from ".$this->table. " AS g,";
    $sql .= $this->grouplinktable. " AS l where ";
    $sql .= " g.released = 1 and l.released = 1 and";
    $sql .= " g.group_id = l.group_id and xml=".Xoonips_Utils::convertSQLStr($xml);
    $result = $this->execute($sql);
    if(!$result){
      return $ret;
    }
                while ($row = $this->fetchArray($result)) {
                        $ret[] = $row['user_detail_id'];
                }
                $this->freeRecordSet($result);
                return $ret;
  }

}
?>
