<?php

class CitiesHandler extends ContentHandler{

	function __construct(){
		$this->Handler('CitiesHandler');
	}

	function createCity() {
		$city = new City();
		//$city->state = new State();
		return $city;
	}

	function searchCities($request = array()) {
		global $ROOT_PATH, $COMMON_VARS;
		//printArray($request);
		$presets = array();
		$selected = $this->DB->SelectAny('SELECT id, name FROM preset');
		$presets_count = count($selected);
		for($i = 0; $i < $presets_count; $i++) $presets[$selected[$i]['id']] = $selected[$i]['name'];
		$packages = array();
		$hostpaths = array();
		$usernames = array();
		$request['conditions'] = array();
		$rels = array();
		$request['sortColumn'] = 'id';
		$request['sortType'] = 'asc';
		if(empty($request['curPageNumber'])) $request['curPageNumber'] = 1;
		if(empty($request['pageRecordsQty'])) $request['pageRecordsQty'] = 20;
		//printArray($request['request']);
		!empty($request['request']['search_name']) and $request['conditions']['name'] = array('~*' => urldecode($request['request']['search_name']));
		!empty($request['request']['search_state']) and $request['conditions']['state'] = array('=' => $request['request']['search_state']);
		!empty($request['request']['search_code']) and $request['conditions'][(int)$request['request']['search_code']] = array('=' => 'ANY(c.area_codes)');
		//printarray($request['conditions']);
		foreach($request['conditions'] as $k => $v) $rels[] = 'and';
		$result = $this->DB->SelectArray(array('city c'), 'count(*)', $request['conditions'], 0, 0, 0, array(), 'CND_COMPLEX', $rels);
		$request['recordsTotal'] = $result[0]['count'];
		$request['pagesTotal'] = ceil($request['recordsTotal'] / $request['pageRecordsQty']);
		$result = $this->DB->SelectArray(array('city c'), 'id, name, name_in_url, date_changed, (\'now\'::date - date_changed::date) AS unmodified, access_flag, date_locked', $request['conditions'], $this->getOrderBy($request), $this->getLimit($request), 0, array(), 'CND_COMPLEX', $rels);
		$count = sizeof($result);
		for($i = 0; $i < $count; $i++) {
			$result[$i]['user_name'] = $this->getUsername($result[$i]['access_flag']);
			$result[$i]['_error'] = '';
			$result[$i]['publish'] = array();
			$query = 'SELECT packagepublish.id, packagepublish.ownerid as package_id, packagepublish.host, packagepublish.userid, packagepublish.date_published FROM packagepublish WHERE packagepublish.type = 1 AND packagepublish.successful = 1 AND packagepublish.date_published > \''.$result[$i]['date_changed'].'\' AND packagepublish.ownerid IN (SELECT DISTINCT packageitem.ownerid FROM packageitem WHERE packageitem.type = 14 AND packageitem.object = \''.$result[$i]['id'].'\') ORDER BY packagepublish.date_published DESC';
			$packagepublish = $this->DB->SelectAny($query);
			$presets_unique = array();
			$c = count($packagepublish);
			$index = 0;
			for($j = 0; $j < $c; $j++){
				if(isset($presets_unique[$packagepublish[$j]['host']])) continue;
				$presets_unique[$packagepublish[$j]['host']] = true;
				$result[$i]['publish'][$index] = $packagepublish[$j];
				$date_published = explode ('.', $packagepublish[$j]['date_published']);
				$result[$i]['publish'][$index]['date_published'] = $date_published[0];
				if(!isset($packages[$packagepublish[$j]['package_id']])){
					$sel = $this->DB->SelectAny('SELECT name FROM package WHERE id = '.$packagepublish[$j]['package_id'].' LIMIT 1');
					$packages[$packagepublish[$j]['package_id']] = empty($sel) ? '[deleted]' : $sel[0]['name'];
				}
				$result[$i]['publish'][$index]['package_name'] = $packages[$packagepublish[$j]['package_id']];
				if(!isset($hostpaths[$packagepublish[$j]['host']])){
					$sel = $this->DB->SelectAny('SELECT name FROM preset WHERE id = '.$packagepublish[$j]['host'].' LIMIT 1');
					$hostpaths[$packagepublish[$j]['host']] = empty($sel) ? '[unknown]' : $sel[0]['name'];
				}
				$result[$i]['publish'][$index]['hostpath'] = $hostpaths[$packagepublish[$j]['host']];
				if(!isset($usernames[$packagepublish[$j]['userid']])){
					$sel = $this->DB->SelectAny('SELECT username FROM member WHERE id = '.$packagepublish[$j]['userid'].' LIMIT 1');
					$usernames[$packagepublish[$j]['userid']] = empty($sel) ? '[unknown]' : $sel[0]['username'];
				}
				$result[$i]['publish'][$index]['username'] = $usernames[$packagepublish[$j]['userid']];
				$index++;
			}
			$result[$i]['published_count'] = count($result[$i]['publish']).'/'.$presets_count;
		}
		$request['curPage'] = $result;
		return $request;
	}

	function selectCities($request = array()) {
		global $ROOT_PATH, $COMMON_VARS;
		$presets = array();
		$selected = $this->DB->SelectAny('SELECT id, name FROM preset');
		$presets_count = count($selected);
		for($i = 0; $i < $presets_count; $i++) $presets[$selected[$i]['id']] = $selected[$i]['name'];
		$packages = array();
		$hostpaths = array();
		$usernames = array();
		if(empty($request['curPageNumber'])) $request['curPageNumber'] = 1;
		if(empty($request['pageRecordsQty'])) $request['pageRecordsQty'] = 20;
		$result = $this->DB->SelectAny('SELECT count(*) FROM city');
		$request['recordsTotal'] = $result[0]['count'];
		$result = $this->DB->SelectAny('SELECT id, name, name_in_url, date_changed, (\'now\'::date - date_changed::date) AS unmodified, access_flag, date_locked FROM city ORDER BY id LIMIT '.$request['pageRecordsQty'].' OFFSET '.(($request['curPageNumber'] - 1) * $request['pageRecordsQty']));
		$count = sizeof($result);
		for($i = 0; $i < $count; $i++) {
			$result[$i]['user_name'] = $this->getUsername($result[$i]['access_flag']);
			$result[$i]['_error'] = '';
			$result[$i]['publish'] = array();
			$query = 'SELECT packagepublish.id, packagepublish.ownerid as package_id, packagepublish.host, packagepublish.userid, packagepublish.date_published FROM packagepublish WHERE packagepublish.type = 1 AND packagepublish.successful = 1 AND packagepublish.date_published > \''.$result[$i]['date_changed'].'\' AND packagepublish.ownerid IN (SELECT DISTINCT packageitem.ownerid FROM packageitem WHERE packageitem.type = 14 AND packageitem.object = \''.$result[$i]['id'].'\') ORDER BY packagepublish.date_published DESC';
			$packagepublish = $this->DB->SelectAny($query);
			$presets_unique = array();
			$c = count($packagepublish);
			$index = 0;
			for($j = 0; $j < $c; $j++){
				if(isset($presets_unique[$packagepublish[$j]['host']])) continue;
				$presets_unique[$packagepublish[$j]['host']] = true;
				$result[$i]['publish'][$index] = $packagepublish[$j];
				$date_published = explode ('.', $packagepublish[$j]['date_published']);
				$result[$i]['publish'][$index]['date_published'] = $date_published[0];
				if(!isset($packages[$packagepublish[$j]['package_id']])){
					$sel = $this->DB->SelectAny('SELECT name FROM package WHERE id = '.$packagepublish[$j]['package_id'].' LIMIT 1');
					$packages[$packagepublish[$j]['package_id']] = empty($sel) ? '[deleted]' : $sel[0]['name'];
				}
				$result[$i]['publish'][$index]['package_name'] = $packages[$packagepublish[$j]['package_id']];
				if(!isset($hostpaths[$packagepublish[$j]['host']])){
					$sel = $this->DB->SelectAny('SELECT name FROM preset WHERE id = '.$packagepublish[$j]['host'].' LIMIT 1');
					$hostpaths[$packagepublish[$j]['host']] = empty($sel) ? '[unknown]' : $sel[0]['name'];
				}
				$result[$i]['publish'][$index]['hostpath'] = $hostpaths[$packagepublish[$j]['host']];
				if(!isset($usernames[$packagepublish[$j]['userid']])){
					$sel = $this->DB->SelectAny('SELECT username FROM member WHERE id = '.$packagepublish[$j]['userid'].' LIMIT 1');
					$usernames[$packagepublish[$j]['userid']] = empty($sel) ? '[unknown]' : $sel[0]['username'];
				}
				$result[$i]['publish'][$index]['username'] = $usernames[$packagepublish[$j]['userid']];
				$index++;
			}
			$result[$i]['published_count'] = count($result[$i]['publish']).'/'.$presets_count;
		}
		$request['pagesTotal'] = ceil($request['recordsTotal'] / $request['pageRecordsQty']);
		$request['curPage'] = $result;
		return $request;
	}

	function getCityLock($request = array()) {
		$query = 'SELECT city.access_flag, city.date_locked, member.username FROM city, member WHERE city.id = '.$request['id'].' AND member.id = city.access_flag LIMIT 1';
		$selected = $this->DB->SelectAny($query);
		return empty($selected[0]) ? array() : $selected[0];
	}

	function lockCity($request = array()) {
		$this->setLogEvent(3, 20, 3, 'Lock City ['.$request['id'].']', $request['id']);
		$this->DB->update('city', array('access_flag' => $this->Security->currentuser->id, 'date_locked' => 'now'), array('id' => $request['id']));
		$this->DB->commit();
		return array();
	}

	function unlockCity($request = array()) {
		$this->setLogEvent(3, 20, 4, 'UnLock City ['.$request['id'].']', $request['id']);
		$this->DB->update('city', array('access_flag' => 0), array('id' => $request['id']));
		$this->DB->commit();
		return array();
	}

	function openCity($request = array()) {
		GLOBAL $ROOT_PATH, $COMMON_VARS, $TOOLS_PATH;
		$city = $this->createCity();
		if(empty($request['undo'])) {
			$city->id = $request['id'];
			$this->DB->initObject($city);
		}
		else {
			$Undo_field = array('type' => 14, 'id' => $request['id'], 'undo' => 0, 'version' => $request['undo']);
			include ($TOOLS_PATH.'/undo.php');
			$city = $print_arr;
		}
		$city->comments = $this->DB->SelectAny('SELECT cc.*, m.username AS member FROM citycomment cc, member m WHERE m.id = cc.member AND cc.ownerid = '.$request['id'].' ORDER BY cc.version DESC LIMIT 10 OFFSET 0');
		$count = count($city->comments);
		for($i = 0; $i < $count; $i++) $city->comments[$i]['file'] = file_exists($ROOT_PATH.'/undo/city/'.$request['id'].'/'.$city->comments[$i]['version'].'.inc') ? true : false;
		return $city;
	}

	function saveCity($request = array()) {
		GLOBAL $ROOT_PATH, $TOOLS_PATH;
		$this->undo = 0;
		$city = $this->createCity();
		$this->parseRequest($request, $city);
		$selected = $this->DB->SelectAny('SELECT id FROM city WHERE name = \''.$city->name.'\' LIMIT 1');
		//if(!empty($selected) && $selected[0]['id'] != $city->id) {
			//$city->_error = 'City with such name already exists';
			//return $city;
		//}
		$selected = $this->DB->SelectAny('SELECT id FROM city WHERE name_in_url = \''.$city->name_in_url.'\' LIMIT 1');
		if(!empty($selected) && $selected[0]['id'] != $city->id) {
			$city->_error = 'City with such name in url already exists';
			return $city;
		}
		if (empty($city->id)) {
			$city->uid = getUnique32();
			$city->date_created = 'now';
			$this->setLogEvent(3, 20, 11, 'Add City "'.$city->name.'"');
		}
		else {
			$this->setLogEvent(3, 20, 12, 'Edit City "'.$city->name.'" ['.$city->id.']', $city->id);
			$Undo_field = array('type' => 14, 'id' => $city->id, 'undo' => 1);
			include($TOOLS_PATH.'/undo.php');
			$this->undo = $undo_handler->arr_obj;
		}
		$city->date_changed = 'now';
		if(empty($city->uid)) $city->uid = getUnique32();
		$this->DB->processObject($city, 'save', array(), array(), TRUE);
		$this->DB->commit();
		$this->DB->delete('deleted', array('uid' => $city->uid, 'type' => 14));
		$this->DB->begin();
		$comment = new CityComment();
		$comment->version = 1;
		$comment->value = 'City has been created';
		$selected = $this->DB->SelectAny('SELECT version FROM citycomment WHERE ownerid = '.$city->id.' ORDER BY date DESC LIMIT 1 OFFSET 0');
		if(!empty($selected[0]['version'])) {
			$comment->version = ++$selected[0]['version'];
			$comment->value = (!empty($request['undo']) ? 'City has been undone up to '.$request['undo'].' version<br>'."\r\n" : '').htmlspecialchars($request['comment']);
		}
		$comment->ownerid = $city->id;
		$comment->member = empty($request['id_user']) ? $this->Security->currentuser->id : $request['id_user'];
		$comment->date = 'now';
		$this->DB->processObject($comment, 'save', array(), array(), TRUE);
		if(!empty($request['undo'])) $this->setLogEvent(3, 20, 10, 'City "'.$city->name.'" ['.$city->id.'] has been undone up to '.$request['undo'].' version', $city->id);
		$this->DB->commit();
		if($this->DB->good && !empty($this->undo)) 	$undo_handler->GetArrayInFile($this->undo, 'Undo_object', $ROOT_PATH.'/undo/'.$pub_type[0]['name'].'/'.$Undo_field['id'].'/'.($comment->version > 1 ? $comment->version - 1 : 1).'.inc');
		return $city;
	}

	function deleteCity($request = array()){
		GLOBAL $REGISTRY, $Server;
		$city = $this->createCity();
		$this->parseRequest($request, $city);
		$this->DB->initObject($city);
		$presets = $this->DB->SelectGuide(array('preset g'), '*', array());
		$this->setLogEvent(3, 20, 13, 'Delete City "'.$city->name.'" ['.$city->id.']',$city->id);
		foreach($presets as $prekey => $prevalue) {
			$selected = $this->DB->SelectAny('SELECT id FROM deleted WHERE uid = \''.$city->uid.'\' AND preset = '.$prekey.' AND type = 14 LIMIT 1');
			if(empty($selected)) $this->DB->insert('deleted', array('uid' => $city->uid, 'preset' => $prekey, 'type' => 14));
		}
		$this->DB->processObject($city, 'delete', array(), array(), TRUE);
		$this->DB->delete('citycomment', array('ownerid' => $city->id));
		$this->DB->commit();
		return $city;
	}

	function getUsername ($id) {
		if(!is_numeric($id)) $id = 0;
		$selected = $this->DB->SelectAny('SELECT username FROM member WHERE id = '.$id.' LIMIT 1');
		return empty($selected) ? '' : $selected[0]['username'];
	}

	function selectCityComments ($request = array()) {
		GLOBAL $ROOT_PATH;
		if(empty($request['curPageNumber'])) $request['curPageNumber'] = 1;
		if(empty($request['pageRecordsQty'])) $request['pageRecordsQty'] = 20;
		$city = $this->DB->SelectAny('SELECT name FROM city WHERE id = '.$request['id'].' LIMIT 1');
		$request['name'] = $city[0]['name'];
		$selected = $this->DB->SelectAny('SELECT cc.*, m.username AS member FROM citycomment cc, member m WHERE m.id = cc.member AND cc.ownerid = '.$request['id'].' ORDER BY cc.version DESC LIMIT '.$request['pageRecordsQty'].' OFFSET '.(($request['curPageNumber'] - 1) * $request['pageRecordsQty']));
		$count = count($selected);
		for ($i = 0; $i < $count; $i++) $selected[$i]['file'] = file_exists($ROOT_PATH.'/undo/city/'.$request['id'].'/'.$selected[$i]['version'].'.inc') ? true : false;
		$count = $this->DB->SelectAny('SELECT count(id) AS count FROM citycomment WHERE ownerid = '.$request['id']);
		$request['recordsTotal'] = $count[0]['count'];
		$request['pagesTotal'] = ceil($request['recordsTotal'] / $request['pageRecordsQty']);
		$request['curPage'] = $selected;
		return $request;
	}
}
?>