<?
require_once( dirname(__FILE__).'/../functions.php');


class businesplan {
	var $groups = array();
	var $fields = array();
	var $_db = null;

	var $id = 0;
	var $champion = 0;

	function businesplan(&$db, $id=null) {
		$this->_db = $db;

		if (is_numeric($id) && $id > 0) {
			$this->id = $id;

			$sql = "SELECT bsp_id, bsp_title, bsp_champion, bsp_state, bsp_usr_id, bsp_video_code, bsp_admin_comments FROM ibp_businessplan WHERE bsp_id = ".$id;
			$rs = $this->_db->query($sql);
			if ($id == $rs->Row('bsp_id')) {

				$this->champion	= $rs->Row('bsp_champion');
				$this->state	= $rs->Row('bsp_state');
				$this->usr_id	= $rs->Row('bsp_usr_id');
				$this->title	= $rs->Row('bsp_title');
				$this->admin_comments	= $rs->Row('bsp_admin_comments');
				
				$this->video_code	= $rs->Row('bsp_video_code');

			} else {
				die("BP [$id] not found");
			}
		}

		$this->groups = array();
		$rhis->fields = array();

		$this->_init_ctrl();

		$this->_clean_bsp_table();
	}

	function _clean_bsp_table() {
		$sql = "DELETE ibp_businessplan.* 
				FROM ibp_businessplan 
				LEFT JOIN ibp_values ON val_bsp_id = bsp_id AND val_id IS NULL
				WHERE bsp_title = 'new' AND bsp_state = 'NEW' AND bsp_id != ".$this->id;
		$this->_db->execute($sql);

	}
	
	function _init_ctrl() {
		if (!isset($GLOBALS['g_obj_ctrl']) || !is_object ($GLOBALS['g_obj_ctrl'])) {
			include_once('htmlcontrols-1.0/class.htmlcontrols.php');
			$GLOBALS['g_obj_ctrl'] = new htmlControls ($this->_settings->general->project->languageCode, $this->_settings->general->paths->virtual->style.'controls.css');
			$GLOBALS['g_obj_ctrl']->_initialized = 1;
			$GLOBALS['g_obj_ctrl']->_init_status['main'] = 1;
		}

		$this->_ctrl =& $GLOBALS['g_obj_ctrl'];
	}


	function create_new() {
		if ((!is_numeric($this->id) || $this->id < 1) && is_numeric($_SESSION['session_usr_id'])) {

			$sql = "INSERT INTO ibp_businessplan SET
						bsp_usr_id		= ".$_SESSION['session_usr_id']."
					,	bsp_title		= 'new'
					,	bsp_state		= 'NEW'
					,	bsp_createdate	= NOW()";

			$this->_db->execute($sql);


			$this->id = $this->_db->lastInsertID();
			$this->usr_id = $_SESSION['session_usr_id'];
			$this->title = 'new';
			$this->state = 'NEW';

			return $this->id;
		}
	}

	function is_owner($usr_id) {
		return $this->usr_id == $usr_id;
	}

	function get_group_data($grp_code) {
		if (isset($this->groups[$grp_code])) {
			return $this->groups[$grp_code];
		}
	}

	function get_data() {
		return $this->groups;
	}

	function get_main_data() {
		return array(
					'id'			=> $this->id
				,	'title'			=> $this->title
				,	'champion'		=> $this->champion
				,	'video_code'	=> $this->video_code
				,	'state'			=> $this->state
				,	'admin_comments' => $this->admin_comments
				);
	}

	function get_field($fld_id) {
		return $this->fields[$fld_id];
	}

	function get_options_by_code($code, $where=array()) {
		
		$sql = "SELECT f1.opt_id, f1.opt_value, f1.opt_name, f1.opt_code, f1.opt_parent_id, count(f2.opt_id) as children
				FROM ibp_fieldoptions AS f1
				INNER JOIN ibp_fields ON f1.opt_fld_id = fld_id AND fld_code = '".$code."'
				LEFT JOIN ibp_fieldoptions AS f2 ON f2.opt_parent_id = f1.opt_id
				".(count($where) > 0 ? ('WHERE '.implode(' AND ',$where)) : '')."
				GROUP BY f1.opt_id
				ORDER BY f1.opt_order";

		$rs = $this->_db->query($sql);
		return rs2arr($rs, 'opt_id', 'opt_name');


	}

	function print_download($fil_id) {
		$sql = "SELECT fil_data, fil_filename, fil_mime, fil_size
				FROM ibp_files
				WHERE fil_id = ". $fil_id ."
				AND	  fil_bsp_id = ".$this->id;
		$rs = $this->_db->query($sql);

		if ($r = $rs->row()) {

			Header("Content-type: ".$r['fil_mime'].", Expires: ".gmdate("D, d M Y H:i:s", time() + 3600));
			header("Content-Disposition: attachment; filename=". $r['fil_filename']);
			header("Content-Length: ".$r['fil_size']);
			echo $r['fil_data'];
		}
		$rs->Close();
		exit();
	}

	function save($data, $data_tree=null) {

		if (!is_array($data)) return;

		//--- merge arrays and maintaining key to data correlations (with '+' operator)
		$merge_data = (array)$data + (array)$_FILES['bsp_values']['name'];

		$sql = "SELECT fld_id, fld_type, fld_code FROM ibp_fields WHERE fld_id IN ( ".implode(',',array_keys($merge_data)) .")";
		$rs = $this->_db->query($sql);
		$field_info = rs2arr($rs, 'fld_id');

		foreach ($data as $fld_id => $value) {

			switch ($field_info[$fld_id]['fld_type']) {

				case 'txt':

					if ($field_info[$fld_id]['fld_code'] == 'BSP_TITLE') {
						$sql = "UPDATE ibp_businessplan SET bsp_title = '".mysql_escape_string(trim($value)) ."' WHERE bsp_id = ".$this->id;
						$this->_db->execute($sql);
					}

				case 'txa':
				case 'num':
					$this->_save_value($fld_id, trim($value));

					break;

				case 'olv':
				case 'rdo':
				case 'chk2':

					if ($field_info[$fld_id]['fld_code'] == 'INDUSTRY') {
						$this->_save_option($fld_id, $data_tree, true);
					} else {
						$this->_save_option($fld_id, $value);
					}
					break;
			}


		}

		if (is_array($_FILES['bsp_values']['name'])) {
			foreach ($_FILES['bsp_values']['name'] as $fld_id => $values) {
				switch ($field_info[$fld_id]['fld_type']) {
					case 'bin+':
					case 'bin':

						foreach ($values as $field_index => $file_name) {

							$file_info = array(	'name'		=> $_FILES['bsp_values']['name'][$fld_id][$field_index]
											,	'type'		=> $_FILES['bsp_values']['type'][$fld_id][$field_index]
											,	'tmp_name'	=> $_FILES['bsp_values']['tmp_name'][$fld_id][$field_index]
											,	'error'		=> $_FILES['bsp_values']['error'][$fld_id][$field_index]
											,	'size'		=> $_FILES['bsp_values']['size'][$fld_id][$field_index]
											,	'title'		=> $_REQUEST['bsp_values'][$fld_id]['title'][$field_index]
											,	'fil_id'	=> $_REQUEST['bsp_values'][$fld_id]['fil_id'][$field_index]
											);

							$action = isset($_REQUEST['fil_action'][$fld_id][$field_index]) ? $_REQUEST['fil_action'][$fld_id][$field_index] : 'new';

							$this->_save_file($fld_id, $file_info, $field_index, $action);
						}


						break;

				}

			}
		}
	}
/*
SELECT val_value, val_version_state, val_version
FROM ibp_values
WHERE val_fld_id = 1 and val_version_state = 'ACCEPT'
ORDER BY val_version desc
limit 1

*/
	function _save_value($fld_id, $value) {

		//--- get current value
		$sql = "SELECT val_value FROM ibp_values
				WHERE
					val_fld_id = ".$fld_id."
				AND val_bsp_id = ".$this->id."
				AND val_version_state = 'ACCEPT'
				ORDER BY val_version DESC
				LIMIT 1";
		$rs = $this->_db->query($sql);
		if ($rs->RowCount == 1) {
			if ($value == $rs->Row('val_value')) {
				//-- current value not changed
				return false;
			}
		}


		$sql = "SELECT val_id FROM ibp_values
				WHERE
					val_fld_id = ". $fld_id ."
				AND val_bsp_id = ". $this->id."
				".($this->champion ? "AND val_version_state = 'WAIT'" : "");

		$rs = $this->_db->query($sql);
		if (is_numeric($rs->Row('val_id'))) {
			//--- (pending) record exists
			$sql = "UPDATE ibp_values SET
						val_value		= '".mysql_escape_string($value) ."'
					,	val_changedate	= NOW()
					WHERE val_id = ". $rs->Row('val_id');

			$this->_db->execute($sql);

		} else {
			//--- create new
			$sql = "INSERT INTO ibp_values SET
						val_fld_id			= ". $fld_id ."
					,	val_bsp_id			= ". $this->id."
					,	val_value			= '".mysql_escape_string($value) ."'
					,	val_version_state	= '". ($this->champion ? 'WAIT' : 'ACCEPT') ."'
					,	val_version			= ".  ($this->champion ? $this->_get_next_version(FIELD_TYPE_VAL, $fld_id) : 1) ."
					,	val_createdate		= NOW()";

			$this->_db->execute($sql);

		}
	}

	function _save_option($fld_id, $value, $olv_tree=false) {

		/*
			if bp is championdeal
				if there is a pending version (state: WAIT) then replace that on
				else create a new verion (with state WAIT)
			else replace old version or create a new one

		*/

		//-- check of record exists

		//--- get current options
		$sql = "SELECT
					v1.val_id
				,	v1.val_value
				,	v1.val_version
				,	max(v2.val_version) as current_version
				,	opt_id
				,	opt_value
				FROM ibp_values AS v1
				LEFT JOIN ibp_values AS v2 ON v2.val_fld_id = ".$fld_id." AND v2.val_bsp_id = ".$this->id." AND v2.val_version_state = 'ACCEPT'
				INNER JOIN ibp_val_options, ibp_fieldoptions ON vop_val_id = v1.val_id AND vop_opt_id = opt_id AND opt_fld_id = v1.val_fld_id
				WHERE
					v1.val_fld_id = ".$fld_id."
				AND v1.val_bsp_id = ".$this->id."
				GROUP BY v1.val_id, opt_id HAVING v1.val_version = current_version
				ORDER BY opt_id";
		$rs = $this->_db->query($sql);
		$selected_options = array_keys(rs2arr($rs, 'opt_id'));


		//--- get new options

		$new_opt_ids = array();
		if ($olv_tree) {
			foreach ($value as $opt_id => $state) {
				if ($opt_id != 1 && trim($state) != '') {
					$new_opt_ids[] = substr($opt_id, 1);
				}
			}
		} else {
			if (is_array($value)) {
				foreach ($value as $opt_id => $state) {
					if ($state == 'on') {
						$new_opt_ids[] = $opt_id;
					}
				}
			} elseif (strlen(trim($value)) > 0) {
				$new_opt_ids[] = $value;
			}
		}

		sort($new_opt_ids, SORT_NUMERIC);

		if ($selected_options == $new_opt_ids) {
			//--- no changes made
			return;
		}


		$sql = "SELECT val_id FROM ibp_values
				WHERE
					val_fld_id = ". $fld_id ."
				AND val_bsp_id = ". $this->id."
				".($this->champion ? "AND val_version_state = 'WAIT'" : "");

		$rs = $this->_db->query($sql);
		if (is_numeric($rs->Row('val_id'))) {
			//--- (pending) record exists
			$val_id = $rs->Row('val_id');
			//-- update changedate
			$this->_db->execute("UPDATE ibp_values SET val_changedate = NOW() WHERE val_id = ". $val_id);

			//-- delete previous selected items
			$this->_db->execute('DELETE FROM ibp_val_options WHERE vop_val_id = '. $val_id);

		} else {
			//--- create new
			$sql = "INSERT INTO ibp_values SET
						val_fld_id			= ". $fld_id ."
					,	val_bsp_id			= ". $this->id."
					,	val_value			= NULL
					,	val_version_state	= '". ($this->champion ? 'WAIT' : 'ACCEPT') ."'
					,	val_version			= ".  ($this->champion ? $this->_get_next_version(FIELD_TYPE_VAL, $fld_id) : 1) ."
					,	val_createdate		= NOW()";

			$this->_db->execute($sql);
			$val_id = $this->_db->lastInsertID();

		}

		//-- save the selected option(s)
		if (count($new_opt_ids) > 0) {
			$sql = "INSERT INTO ibp_val_options (vop_val_id, vop_opt_id) VALUES (".$val_id .",". implode("), (".$val_id.",",$new_opt_ids) .')';
			$this->_db->execute($sql);
		}

	}

	function _save_file($fld_id, $file_info, $field_index, $action) {

		switch ($action) {
			case 'new':
				if ($file_info['error'] != UPLOAD_ERR_OK) {
					return;
				}

				$sql = "INSERT INTO ibp_files SET
							fil_bsp_id		= ". $this->id ."
						,	fil_fld_id		= ". $fld_id ."
						,	fil_title		= '".mysql_escape_string($file_info['title'])."'
						,	fil_data		= '".addslashes(file_get_contents($file_info['tmp_name']))."'
						,	fil_filename	= '".mysql_escape_string($file_info['name'])."'
						,	fil_mime		= '".mysql_escape_string($file_info['type'])."'
						,	fil_size		= ". $file_info['size']."
						,	fil_field_index	= ". $field_index."
						,	fil_version		= ". ($this->champion ? $this->_get_next_version(FIELD_TYPE_FIL, $fld_id, $field_index) : 1) ."
						,	fil_version_state	= '". ($this->champion ? 'WAIT' : 'ACCEPT') ."'
						,	fil_createdate	= NOW()";

				$this->_db->execute($sql);

				break;

			case 'save':
				//--- only title is possible changed
				$sql = "UPDATE ibp_files SET
							fil_title		= '".mysql_escape_string($file_info['title'])."'
						,	fil_changedate	= NOW()
						WHERE
							fil_id = ".$file_info['fil_id']."
						AND	fil_bsp_id = ".$this->id."
						AND fil_title != '".mysql_escape_string($file_info['title'])."'";
				$this->_db->execute($sql);

				break;

			case 'delete':
				//-- not possible!
				break;

			case 'replace':
				//---
				if ($file_info['error'] != UPLOAD_ERR_OK) {
					return;
				}

				//--- delete old one
				$sql = "DELETE FROM ibp_files
						WHERE
							fil_id = ".$file_info['fil_id']."
						AND fil_bsp_id = ".$this->id .'
						'.(($this->champion) ? "AND fil_version_state = 'WAIT'" : '').'
						AND fil_field_index = '.$field_index;
				$this->_db->execute($sql);

				$sql = "INSERT INTO ibp_files SET
							fil_bsp_id		= ". $this->id ."
						,	fil_fld_id		= ". $fld_id ."
						,	fil_title		= '".mysql_escape_string($file_info['title'])."'
						,	fil_data		= '".addslashes(file_get_contents($file_info['tmp_name']))."'
						,	fil_filename	= '".mysql_escape_string($file_info['name'])."'
						,	fil_mime		= '".mysql_escape_string($file_info['type'])."'
						,	fil_size		= ". $file_info['size']."
						,	fil_field_index	= ". $field_index."
						,	fil_version		= ".  ($this->champion ? $this->_get_next_version(FIELD_TYPE_FIL, $fld_id, $field_index) : 1) ."
						,	fil_version_state	= '". ($this->champion ? 'WAIT' : 'ACCEPT') ."'
						,	fil_createdate	= NOW()";
				$this->_db->execute($sql);

				break;


		}
	}

	function _get_next_field_index($fld_id) {
		$sql = "SELECT MAX(fil_field_index) + 1 AS next_field_index FROM ibp_files WHERE fil_fld_id = ".$fld_id." AND fil_bsp_id = ". $this->id;
		$rs = $this->_db->query($sql);
		$next_field_index = is_numeric($rs->row('next_field_index')) ? $rs->row('next_field_index') : 1;
		$rs->Close();

		return $next_field_index;
	}

	function _get_next_version($field_type, $fld_id, $field_index=null) {

		if ($field_type == FIELD_TYPE_VAL) {

			$sql = "SELECT MAX(val_version) + 1 AS next_version FROM ibp_values WHERE val_fld_id = ".$fld_id." AND val_bsp_id = ". $this->id;

		} elseif ($field_type == FIELD_TYPE_FIL) {

			$sql = "SELECT MAX(fil_version) + 1 AS next_version FROM ibp_files WHERE fil_fld_id = ".$fld_id." AND fil_bsp_id = ". $this->id ." AND fil_field_index = ". $field_index;

		}

		$rs = $this->_db->query($sql);
		$next_version = is_numeric($rs->row('next_version')) ? $rs->row('next_version') : 1;
		$rs->Close();

		return $next_version;
	}


	function _lookup_data($edit_mode=false, $grp_code=null) {
		if (is_numeric($this->id)) {

			//--- get field_groups
			$rs = $this->_db->query('SELECT grp_id, grp_code, grp_name, grp_desc FROM ibp_fieldgroups ORDER BY grp_order');
			$this->groups = rs2arr($rs, 'grp_code');

			//--- get fields

			$sql = "SELECT
						fld_id, fld_grp_id, grp_code, fld_code, fld_name, fld_type, fld_mandatory, fld_max_length, fld_level, fld_desc, fld_order
					,	v2.val_id				AS id
					,	v2.val_value			AS value
					,	v2.val_version			AS version
					,	v2.val_version_state	AS version_state
					,	UNIX_TIMESTAMP(IFNULL(v2.val_changedate, v2.val_createdate)) AS changedate
					,	MAX(v1.val_version)		AS current_version
					,	MAX(v3.val_version)		AS latest_version
					,	COUNT(opt_id)			AS available_options
					FROM ibp_fields
					INNER JOIN ibp_fieldgroups ON grp_id = fld_grp_id ".($grp_code ? (" AND grp_code = '".$grp_code."'") : '')."
					LEFT JOIN ibp_values AS v1 ON v1.val_fld_id = fld_id AND v1.val_bsp_id = ".$this->id." AND v1.val_version_state = 'ACCEPT'
					LEFT JOIN ibp_values AS v3 ON v3.val_fld_id = fld_id AND v3.val_bsp_id = ".$this->id." AND v3.val_version_state != 'REJECT'
					LEFT JOIN ibp_values AS v2 ON v2.val_fld_id = fld_id AND v2.val_bsp_id = ".$this->id."
					LEFT JOIN ibp_fieldoptions ON opt_fld_id = fld_id
					WHERE fld_level <= 3
					GROUP BY fld_id, id
					ORDER BY grp_order, fld_order, version DESC";

			$rs = $this->_db->query($sql);
			$cur_fld_id = '';
			while ($r = $rs->Row()) {

				if ($cur_fld_id != $r['fld_id']) {
					$cur_fld_id = $r['fld_id'];

					$field_data = $r;
					unset($field_data['id']);
					unset($field_data['value']);
					unset($field_data['version']);
					unset($field_data['version_state']);
					unset($field_data['changedate']);
					unset($field_data['current_version']);
					unset($field_data['latest_version']);
					unset($field_data['available_options']);
					$this->fields[$r['fld_id']] = $field_data;

					$this->fields[$r['fld_id']]['values'] = array();

					$this->groups[$r['grp_code']]['fields'][$r['fld_id']] =& $this->fields[$r['fld_id']];

				}



				if (is_numeric($r['id'])) {
					$this->fields[$r['fld_id']]['values'][$r['id']] = array(	'id'				=> $r['id']
																			,	'value'				=> $r['value']
																			,	'version'			=> $r['version']
																			,	'version_state'		=> $r['version_state']
																			,	'changedate'		=> $r['changedate']
																			,	'current_version'	=> $r['current_version']
																			,	'available_options'	=> $r['available_options']);


					if ($r['available_options'] > 0) {
						//-- this type has options
						$this->fields[$r['fld_id']]['values'][$r['id']]['options'] = $this->_get_options($r['fld_id'], $r['id'], false);

						$opt_list = '';
						foreach ($this->fields[$r['fld_id']]['values'][$r['id']]['options'] as $opt) {
							if ($opt['selected']) {
								$opt_list .= (strlen($opt_list) > 0 ? ', ': '') . $opt['opt_name'];
							}
						}
						$this->fields[$r['fld_id']]['values'][$r['id']]['value'] = $opt_list;
					}

					if ($r['version'] == $r['current_version']) {
						$this->fields[$r['fld_id']]['current_version'] =& $this->fields[$r['fld_id']]['values'][$r['id']];

						if ($r['fld_code'] == 'BSP_TITLE') {
							$this->title = $r['value'];
							$sql = "UPDATE ibp_businessplan SET bsp_title = '".mysql_escape_string($r['value'])."' WHERE bsp_id = ".$this->id;
							$this->_db->execute($sql);
						}
					}
					if ($r['version'] == $r['latest_version']) {
						$this->fields[$r['fld_id']]['latest_version'] =& $this->fields[$r['fld_id']]['values'][$r['id']];

						if ($edit_mode) {
							$this->_make_ctrl($r['fld_id'], $r['id']);
						}
					}

				} elseif ($r['fld_type'] == 'bin' || $r['fld_type'] == 'bin+') {

					//-- get files
					$sql = "SELECT
								f1.fil_id
							,	f1.fil_field_index
							,	f1.fil_title
							,	f1.fil_filename
							,	f1.fil_mime
							,	f1.fil_size
							,	f1.fil_version
							,	f1.fil_version_state
							,	MAX(f2.fil_version) AS latest_version
							,	MAX(f3.fil_version) AS current_version
							,	UNIX_TIMESTAMP(IFNULL(f1.fil_changedate, f1.fil_createdate)) AS changedate
							FROM ibp_files AS f1
							LEFT JOIN ibp_files AS f2 ON f2.fil_bsp_id = f1.fil_bsp_id AND f2.fil_fld_id = f1.fil_fld_id AND f2.fil_field_index = f1.fil_field_index AND f1.fil_version_state != 'REJECT'
							LEFT JOIN ibp_files AS f3 ON f3.fil_bsp_id = f1.fil_bsp_id AND f3.fil_fld_id = f1.fil_fld_id AND f3.fil_field_index = f1.fil_field_index AND f3.fil_version_state = 'ACCEPT'
							WHERE
								f1.fil_bsp_id = ".$this->id."
							AND	f1.fil_fld_id = ".$r['fld_id']."
							GROUP BY f1.fil_id
							ORDER BY f1.fil_version";

					$rs_fil = $this->_db->query($sql);
					while ($fil = $rs_fil->Row()) {
						$this->fields[$r['fld_id']]['values'][$fil['fil_field_index']][$fil['fil_id']]
															= array(	'id'				=> $fil['fil_id']
																	,	'value'				=> ''
																	,	'fil_title'			=> $fil['fil_title']
																	,	'fil_mime'			=> $fil['fil_mime']
																	,	'fil_filename'		=> $fil['fil_filename']
																	,	'fil_size'			=> $fil['fil_size']
																	,	'fil_field_index'	=> $fil['fil_field_index']
																	,	'version'			=> $fil['fil_version']
																	,	'version_state'		=> $fil['fil_version_state']
																	,	'changedate'		=> $fil['changedate']
																	,	'current_version'	=> $fil['current_version']
//																	,	'available_options'	=> $fil['available_options']
																	);

						if ($fil['fil_version'] == $fil['current_version']) {
							$this->fields[$r['fld_id']]['current_version'][$fil['fil_field_index']] =& $this->fields[$r['fld_id']]['values'][$fil['fil_field_index']][$fil['fil_id']];
						}
						if ($fil['fil_version'] == $fil['latest_version']) {
							$this->fields[$r['fld_id']]['latest_version'][$fil['fil_field_index']] =& $this->fields[$r['fld_id']]['values'][$fil['fil_field_index']][$fil['fil_id']];
							if ($edit_mode) {
								$this->_make_ctrl($r['fld_id'], $fil['fil_id'], $fil['fil_field_index']);
							}
						}

						$rs_fil->moveNext();
					}

					//-- bin+ is multiple upload, so always add an empty upload field
					if ($edit_mode && ($r['fld_type'] == 'bin+' || $rs_fil->RowCount == 0)) {
						$this->_make_ctrl($r['fld_id'], null, $this->_get_next_field_index($r['fld_id']));
					}

				} else {
					if ($r['available_options'] > 0) {
						$this->fields[$r['fld_id']]['options'] = $this->_get_options($r['fld_id']);
					}
					if ($edit_mode) {
						$this->_make_ctrl($r['fld_id']);
					}
				}

				$rs->moveNext();
			} # end whlie

		}
	}
	function _get_options($fld_id, $val_id=null, $selected_only=true) {
		if ($selected_only && is_numeric($val_id)) {
			$sql = "SELECT f1.opt_id, f1.opt_value, f1.opt_name, f1.opt_code, f1.opt_parent_id, count(f2.opt_id) as children
					FROM ibp_fieldoptions AS f1
					INNER JOIN ibp_val_options ON vop_opt_id = f1.opt_id AND vop_val_id = ". $val_id."
					LEFT JOIN ibp_fieldoptions AS f2 ON f2.opt_parent_id = f1.opt_id
					WHERE f1.opt_fld_id = ".$fld_id."
					GROUP BY f1.opt_id, vop_val_id
					ORDER BY f1.opt_order";
		} elseif (is_numeric($val_id)) {
			$sql = "SELECT f1.opt_id, f1.opt_value, f1.opt_name, f1.opt_code, f1.opt_parent_id, !ISNULL(vop_val_id) as selected, count(f2.opt_id) as children
					FROM ibp_fieldoptions AS f1
					LEFT JOIN ibp_val_options ON vop_opt_id = f1.opt_id AND vop_val_id = ". $val_id."
					LEFT JOIN ibp_fieldoptions AS f2 ON f2.opt_parent_id = f1.opt_id
					WHERE f1.opt_fld_id = ".$fld_id."
					GROUP BY f1.opt_id, vop_val_id
					ORDER BY f1.opt_order";
		} else {
			$sql = "SELECT f1.opt_id, f1.opt_value, f1.opt_name, f1.opt_code, f1.opt_parent_id, count(f2.opt_id) as children
					FROM ibp_fieldoptions AS f1
					LEFT JOIN ibp_fieldoptions AS f2 ON f2.opt_parent_id = f1.opt_id
					WHERE f1.opt_fld_id = ".$fld_id."
					GROUP BY f1.opt_id
					ORDER BY f1.opt_order";
		}
		$rs = $this->_db->query($sql);

		return rs2arr($rs, 'opt_id');
	}

	function _make_ctrl($fld_id, $val_id=null, $field_index=null) {
		if (isset($this->fields[$fld_id])) {

			$ctrl_prepend = '';
			$ctrl_append = '';

			$type		= $this->fields[$fld_id]['fld_type'];
			$label		= $this->fields[$fld_id]['fld_name'];

			$name		= 'bsp_values['.$fld_id.']';
			if (is_numeric($val_id)) {
				$value	= $this->fields[$fld_id]['values'][$val_id]['value'];
			} else {
				$value	= '';
			}
			$maxlength	= is_numeric($this->fields[$fld_id]['fld_max_length']) ? $this->fields[$fld_id]['fld_max_length'] : null;
			$argv		= null;
			$regexp		= null;
			$flags		= $this->fields[$fld_id]['fld_mandatory'] ? CTRL_MANDATORY : '';

			switch ($this->fields[$fld_id]['fld_type']) {
				case 'txt':
				case 'txa':
					$value = stripslashes($value);
					break;


				case 'olv':

					if ($this->fields[$fld_id]['fld_code'] == 'INDUSTRY') {
						//-- 2 level olv list

						$options = $this->_get_options($fld_id, $val_id, false);
//echo "<pre>";
//print_r($options);
//echo "</pre>";

						$ctrl_prepend = '<style>';
						$nodes = array();
						foreach ($options as $opt) {
							$nodes['c'.$opt['opt_id']]->label			= addslashes($opt['opt_name']);
							$nodes['c'.$opt['opt_id']]->name			= 'cat'.$opt['opt_id'];
							$nodes['c'.$opt['opt_id']]->parent			= (is_numeric($opt['opt_parent_id']) ? 'c'.$opt['opt_parent_id'] : 1);
							$nodes['c'.$opt['opt_id']]->initial_state	= true;
							$nodes['c'.$opt['opt_id']]->checked			= $opt['selected'] ? true : false;

							if (!is_numeric($opt['opt_parent_id']) && $opt['children'] > 0) $ctrl_prepend .= "\n#bsp_values_INDUSTRYcheckc".$opt['opt_id'] ." div {display:none;}\n#bsp_values_INDUSTRYcheckc".$opt['opt_id'] ." {width:10px;}\n";
						}
						$ctrl_prepend .= '</style>';

						$ctrl_append = "<script>expand_tree('bsp_values_INDUSTRY',1,true)</script>";

						$nodes[1]->label	= 'Select industry';
						$nodes[1]->name		= 'root';
						$nodes[1]->parent	= 0;

						$tree = new stdClass();
						$tree->indent		= 10;
						$tree->checkable	= true;
						$tree->autoselect	= false;
						$tree->nodes		= &$nodes;

						$name = 'bsp_values_INDUSTRY';
						$type = 'olv';
						$argv = $tree;
						$value = null;
//						echo $ctrl->olv('Navigation', 'kwd_tree', null, 255, $tree);

					}

					break;

				case 'chk2':
				case 'rdo':
					$options = array();

					if (is_numeric($val_id)) {
						$options = $this->fields[$fld_id]['values'][$val_id]['options'];
					} else {
						$options = $this->fields[$fld_id]['options'];
					}

					foreach ((array)$options as $opt_id => $opt) {
						$options[$opt_id] = $opt['opt_name'];

						if ($this->fields[$fld_id]['fld_type'] == 'rdo' && $opt['selected']) $value = $opt_id;
						elseif ($this->fields[$fld_id]['fld_type'] == 'chk2' && $opt['selected'] && count($options) > 1) $argv['checked'][$opt_id] = true;
					}

					$argv['options'] = $options;

					break;

				case 'bin+':
				case 'bin':
					$type = 'bin';
					if (is_numeric($field_index)) {

						if (isset($this->fields[$fld_id]['values']) && is_array($this->fields[$fld_id]['values']) && count($this->fields[$fld_id]['values']) > 0) {
							$ctrl_name = $name . '['. $field_index .']';

							$this->fields[$fld_id]['ctrl'][$field_index] = $this->_ctrl->bin($label, $ctrl_name, $value, $maxlength, $argv, $regexp, null);
							$this->fields[$fld_id]['ctrl'][$field_index] .= $this->_ctrl->hdn('bsp_values['.$fld_id.'][fil_id]['. $field_index .']', $this->fields[$fld_id]['latest_version'][$field_index]['id']);
							$this->fields[$fld_id]['ctrl_title'][$field_index] = $this->_ctrl->txt('Title', 'bsp_values['.$fld_id.'][title]['. $field_index .']', $this->fields[$fld_id]['latest_version'][$field_index]['fil_title']);
						} else {
							//$field_index = 1;
							$ctrl_name = $name . '['. $field_index .']';
							$this->fields[$fld_id]['ctrl'][$field_index] = $this->_ctrl->bin($label, $ctrl_name, $value, $maxlength, $argv, $regexp, $flags);

							$this->fields[$fld_id]['ctrl_title'][$field_index] = $this->_ctrl->txt('Title', 'bsp_values['.$fld_id.'][title]['. $field_index .']', $this->fields[$fld_id]['latest_version'][$field_index]['fil_title']);
						}
					}
/*					if ($this->fields[$fld_id]['fld_type'] == 'bin+') {

						//-- bin+ is multiple upload, so always add an empty upload field
						$next_field_index = $this->_get_next_field_index($fld_id);
						$ctrl_name = $name . '['. $next_field_index .']';

						$this->fields[$fld_id]['ctrl'][$next_field_index] = $this->_ctrl->bin($label, $ctrl_name, $value, $maxlength, $argv, $regexp, $flags);
						$this->fields[$fld_id]['ctrl_title'][$next_field_index] = $this->_ctrl->txt('Title', 'bsp_values['.$fld_id.'][title]['. $field_index .']', $this->fields[$fld_id]['latest_version'][$next_field_index]['fil_title']);


					}
*/

					$ctrl_added = true;
					break;

				case 'num':
					break;

				case 'cmb':
					$flags |= CTRL_READONLY;
					break;



			}

			if (!$ctrl_added) {
				$this->fields[$fld_id]['ctrl'] =	$ctrl_prepend
												.	$this->_ctrl->{$type}($label, $name, $value, $maxlength, $argv, $regexp, $flags)
												.	$ctrl_append;
			}

		}


	}

}

?>