<?php
include_once(__ENGINE_PATH.'/functions/subfunctions/file_functions.php');
class Cron
{
	protected $db;
	
	public function __construct(&$db)
	{
		$this->db=&$db;
	}

	/**
	 * Блок функций для контроля исполнения cron
	 */

	/**
	 * Запись в базу в момент запуска крона
	 * @param $name
	 * @param $interval
	 * @return int OR false
	 */
	public function cron_start($name, $interval){
		$sql="INSERT INTO `cron_report` (`name`,`interval`,`starttime`) VALUES('{$name}', {$interval}, NOW())";
		if(!$this->db->query($sql)) {
			$this->cron_bugreport("Ошибка старта крон-задания: {$name}.");
			return false;
		}

		return $this->db->get_insert_id();
	}

	/**
	 * Промежуточное обновления крон отчёта
	 * @param $id
	 * @param $data
	 * @return bool
	 */
	public function cron_update_report($id, $data){
		$sql="UPDATE `cron_report` SET `report_data`='{$data}' WHERE `id`={$id}";
		if(!$this->db->query($sql)) return false;

		return true;
	}

	/**
	 * Получить время последнего выполнения крона по name
	 * @param $name
	 * @return datetime OR false
	 */
	public function get_last_runtime($name){
		$sql = "SELECT `starttime` FROM `cron_report` WHERE `name`='{$name}' ORDER BY `id` DESC LIMIT 1";
		$this->db->query($sql);
		$result = $this->db->get_result();
		if(count($result)){
			return $result[0]['starttime'];
		}
		return false;
	}

	/**
	 *
	 * Крон завершен - обновить отчёт
	 * @param $id
	 * @param $data
	 * @return bool
	 */
	public function cron_stop($id, $data){
		$sql="UPDATE `cron_report` SET `stoptime`=NOW(), `report_data`='{$data}' WHERE `id`={$id}";
		if(!$this->db->query($sql)) {
			$this->cron_bugreport("Ошибка остановки крон-задания: #{$id}.");
			file_put_contents(__LOG_ERROR_PATH.'/cronstop.txt',gmdate('d-m-Y H:i')."SQL: {$sql}, ID: {$id}\n",FILE_APPEND);
			return false;
		}

		return true;
	}

	/**
	 * Отправка ошибки админу
	 * @param $msg
	 */
	public function cron_bugreport($msg){
		include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
		$mailer=new Mailer();
		$mailer->mail(__SUPPORT_EMAIL, 'Багрепорт: ошибка при выполнении cron-задачи', $msg);
	}

	/**
	 * Список отчётов
	 * @param string $where
	 * @return array
	 */
	public function cron_get_reports($where = ''){
		$sql = "SELECT * FROM `cron_report`";
		if(strlen($where)) $sql .= " WHERE {$where}";
		$sql .= " ORDER BY `id` DESC";
		$this->db->query($sql);
		return $this->db->get_result();
	}

	/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	//add remined event to write review
	public function remind_users_make_reviews(){

		$func_report = array("users_count"=>0, "guest_count"=>0);
		$users_no_visits = array();//юзеры, которые возможно долго не заходили на сайт и получается, что не видили первое оповещение(спустя 4 дня)
		$user_timetables = array();

		$sql="SELECT DISTINCTROW UNIX_TIMESTAMP(t.`date`) as `date`,t.`id` as `timetable_id`,t.`date_text`,a.`id` as `action_id`,a.`type`, a.`alias` as `action_link`, a.`name` as `action_name`,o.`user_id`
		FROM `timetable_orders` o force index (`user_id_type`)
		INNER JOIN `timetable` t ON o.`timetable_id`=t.`id` 
		INNER JOIN `actions` a ON t.`action_id`=a.`id` 
		LEFT JOIN `reviews` r ON r.`user_id`=o.`user_id` AND r.`object_type`=3 AND r.`object_id`=a.`id` 
		WHERE 	o.`type`<3 AND 
				o.`user_id`>0 AND 
				r.`id` is NULL AND 
				(
					(a.`type` NOT IN(2,3,6) AND (DATE(t.`date`)=DATE(NOW()-INTERVAL 4 DAY) OR DATE(t.`date`)=DATE(NOW()-INTERVAL 14 DAY)) )
					OR 
					(a.`type` IN(2,3) AND DATE(o.`date`)=DATE(NOW()-INTERVAL 14 DAY))
				) AND 
				o.`user_id`!=a.`owner_id`  
		ORDER BY o.`user_id`";

		$this->db->query($sql);
		$result=$this->db->get_result();
		if(count($result)){

			include_once(__ENGINE_PATH.'/functions/classes/class_eventer.php');
			$eventer=new Eventer($this->db);
			
			foreach($result as $item){

				//записываем id юзеров, чтобы проверить дату последнего визита сайта
				if(gmdate("d", $item["date"]) === gmdate("d", (time()-14*24*3600)) || in_array($item["type"], array(2,3,6))){
					$users_no_visits[] = $item["user_id"];
					$user_timetables[$item["user_id"]][] = array("action_link"=>$item["action_link"],"action_name"=>$item["action_name"],"timetable_id"=>$item["timetable_id"]);
				}

				$eventer->add_event(1,10,array($item['user_id']=>$item['user_id']),0,$item['action_id'],3,$item['timetable_id'],6,'',gmdate("Y-m-d",time()));
			}
		}

		//теперь проверим дату последнего посещения
		if(count($users_no_visits)){

			$sql="SELECT MAX(v.`date`) as `date`, v.`user_id`,u.`email`,u.`name`
			FROM `visits` v
				JOIN `users` u ON u.`id`=v.`user_id`
			WHERE
				v.`user_id` IN (".join(", ", $users_no_visits).")
			GROUP BY
				v.`user_id`
			HAVING
				MAX( v.`date`) <= (NOW()-INTERVAL 10 DAY)
			ORDER BY
				v.`date` DESC
			";

			$this->db->query($sql);
			$result3=$this->db->get_result();
			if(count($result3)){

				global $object_types;
				include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
				$mailer = new Mailer();

				foreach($result3 as $value){

					if(!trim($value["email"]))
						continue;

					foreach($user_timetables[$value["user_id"]] as $timetable_data){
						$msg = "";
						$subject = "";
						$action_link="/".$object_types[3]['url']."/".$timetable_data["action_link"]."/".($timetable_data["timetable_id"] ? "?date=".$timetable_data["timetable_id"] : "");
						$action_name=$timetable_data["action_name"];
						$user_name=$value["name"];
						include(__ENGINE_PATH."/templates/email.user_review_remind.php");
						if(@$mailer->mail($value["email"], $subject, $msg)) $func_report["users_count"] += 1;
					}
				}

			}
		}

		//напоминаем об отзыве гостям, подававшим заявку
		$sql = "
			SELECT
				t.`id` as `timetable_id`, a.`alias` as `action_link`, a.`name` as `action_name`, o.`email`, o.`name` as `guest_name`
			FROM `timetable_orders` o
				INNER JOIN `timetable` t ON o.`timetable_id`=t.`id` 
				INNER JOIN `actions` a ON t.`action_id`=a.`id` 
				
			WHERE
				o.`type`<3
				AND o.`user_id`=0
				AND o.`email` != ''
				AND (
					(a.`type` NOT IN(2,3,6) AND DATE(t.`date`)=DATE(NOW()-INTERVAL 4 DAY))
					OR
					(a.`type` IN(2,3) AND DATE(o.`date`)=DATE(NOW()-INTERVAL 14 DAY))
				)
						
			GROUP BY
				o.`order_id`
		";
		$this->db->query($sql);
		$result2=$this->db->get_result();
		if(count($result2)){

			global $object_types;
			include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
			$mailer = new Mailer();
			
			foreach($result2 as $value){
				
				if(!trim($value["email"]))
					continue;
				
				$msg = "";
				$subject = "";
				$action_link="/".$object_types[3]['url']."/".$value["action_link"]."/".($value["timetable_id"] ? "?date=".$value["timetable_id"] : "");
				$action_name=$value["action_name"];
				$guest_name=$value["guest_name"];
				include(__ENGINE_PATH."/templates/email.guest_review_remind.php");
				if(@$mailer->mail($value["email"], $subject, $msg)) $func_report["guest_count"] += 1;
			}
			
		}

		return "пользователям отправлено писем - ".$func_report["users_count"].", гостям отправлено писем - ".$func_report["guest_count"];
		
	}

	
	//send reminder to check actual information to object`s owners
	public function actuality_broodcast($actuality_days=180)
	{
		$mail_count=0;
		$sql='';
		global $object_types;
		$result=array();
		foreach($object_types as $key=>$object_type)
		{
			if(in_array($key,array(2,3,6,7,8,12,13,14)))continue;
			elseif($key==1){
				$sql="SELECT UNIX_TIMESTAMP(o.`created`) as `created`,o.`id`,u.`email`,u.`login`,u.`name` as `user_name`,u.`surname` ,COALESCE(h.`action_type`,0) as `action_type`, DATEDIFF( NOW(), MAX(COALESCE(h.`date`,o.`created`) )) AS  `exist` ,o.`owner_id`,o.`name`,o.`alias`,{$key} as `object_type`, 0 as `type`, obr.`region`, obr.`id_object_regions`
				FROM `{$object_type['class']}` o 
				LEFT JOIN `users` u ON o.`owner_id`=u.`id`
				LEFT JOIN `object_regions` obr ON obr.`object_type` IN(1,5) AND obr.`object_id`=o.`id` AND obr.`main_link`=1 
				LEFT JOIN `objects_history` h ON h.`object_id`=o.`id` AND h.`object_type`={$key} AND h.`action_type` IN(1,2,3) /*AND h.`user_id`=o.`owner_id`*/
				WHERE o.`moderated`=1 AND o.`block`=0 AND o.`is_treiner`=1 AND o.`owner_id`!=30 AND u.`group`=1
				GROUP BY o.`id`
				HAVING (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))>0) AND (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))%{$actuality_days})=0
				ORDER BY MAX(COALESCE(h.`date`,o.`created`)) ASC,`id_object_regions` ASC";
				$this->db->query($sql);
				$result=array_merge($result,$this->db->get_result());	
			}elseif($key==5){
				$sql="SELECT UNIX_TIMESTAMP(o.`created`) as `created`,o.`id`,u.`email`,u.`login`,u.`name` as `user_name`,u.`surname` ,COALESCE(h.`action_type`,0) as `action_type`, DATEDIFF( NOW(), MAX(COALESCE(h.`date`,o.`created`) )) AS  `exist` ,o.`owner_id`,o.`name`,o.`alias`,{$key} as `object_type`, 0 as `type`, obr.`region`, obr.`id_object_regions`
				FROM `{$object_type['class']}` o 
				LEFT JOIN `users` u ON o.`owner_id`=u.`id` 
				LEFT JOIN `object_regions` obr ON obr.`object_type` IN(1,5) AND obr.`object_id`=o.`id` AND obr.`main_link`=1 
				LEFT JOIN `objects_history` h ON h.`object_id`=o.`id` AND h.`object_type`={$key} AND h.`action_type` IN(1,2,3) /*AND h.`user_id`=o.`owner_id`*/
				WHERE o.`moderated`=1 AND o.`block`=0 AND o.`is_treiner`=0 AND o.`is_consultant`=1 AND o.`owner_id`!=30 AND u.`group`=1
				GROUP BY o.`id`
				HAVING (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))>0) AND (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))%{$actuality_days})=0
				ORDER BY MAX(COALESCE(h.`date`,o.`created`)) ASC,`id_object_regions` ASC";
				$this->db->query($sql);
				$result=array_merge($result,$this->db->get_result());	
			}elseif($key==9){
				include_once(__ENGINE_PATH."/functions/classes/class_esoterics.php");
				$esoterics=new Esoterics($this->db);
				foreach($esoterics->esoteric_type as $key2=>$type)
				{
					$sql="SELECT UNIX_TIMESTAMP(o.`created`) as `created`,o.`id`,u.`email`,u.`login`,u.`name` as `user_name`,u.`surname` ,COALESCE(h.`action_type`,0) as `action_type`, DATEDIFF( NOW(), MAX(COALESCE(h.`date`,o.`created`) )) AS  `exist` ,o.`owner_id`,o.`name`,o.`alias`,9 as `object_type`, o.`type`, obr.`region`, obr.`id_object_regions`
					FROM `{$object_type['class']}` o 
					LEFT JOIN `users` u ON o.`owner_id`=u.`id` 
					LEFT JOIN `object_regions` obr ON obr.`object_type`={$key} AND obr.`object_id`=o.`id` AND obr.`main_link`=1 
					LEFT JOIN `objects_history` h ON h.`object_id`=o.`id` AND h.`object_type`={$key} AND h.`action_type` IN(1,2,3) /*AND h.`user_id`=o.`owner_id`*/
					WHERE o.`moderated`=1 AND o.`block`=0 AND o.`type`={$key2} AND o.`owner_id`!=30 AND u.`group`=1
					GROUP BY o.`id`
					HAVING (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))>0) AND (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))%{$actuality_days})=0
					ORDER BY MAX(COALESCE(h.`date`,o.`created`)) ASC,`id_object_regions` ASC";
					$this->db->query($sql);
					$result=array_merge($result,$this->db->get_result());	
				}
			}elseif($key!=12){
				$sql="SELECT UNIX_TIMESTAMP(o.`created`) as `created`,o.`id`,u.`email`,u.`login`,u.`name` as `user_name`,u.`surname` ,COALESCE(h.`action_type`,0) as `action_type`, DATEDIFF( NOW(), MAX(COALESCE(h.`date`,o.`created`) )) AS  `exist` ,o.`owner_id`,o.`name`,o.`alias`,{$key} as `object_type`, 0 as `type`, obr.`region`, obr.`id_object_regions`
				FROM `{$object_type['class']}` o 
				LEFT JOIN `users` u ON o.`owner_id`=u.`id` 
				LEFT JOIN `object_regions` obr ON obr.`object_type`={$key} AND obr.`object_id`=o.`id` AND obr.`main_link`=1 
				LEFT JOIN `objects_history` h ON h.`object_id`=o.`id` AND h.`object_type`={$key} AND h.`action_type` IN(1,2,3) /*AND h.`user_id`=o.`owner_id`*/
				WHERE o.`moderated`=1 AND o.`block`=0 AND o.`owner_id`!=30 AND u.`group`=1
				GROUP BY o.`id`
				HAVING (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))>0) AND (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))%{$actuality_days})=0
				ORDER BY MAX(COALESCE(h.`date`,o.`created`)) ASC,`id_object_regions` ASC";
				$this->db->query($sql);
				$result=array_merge($result,$this->db->get_result());	
			}else{
				$sql="SELECT UNIX_TIMESTAMP(o.`created`) as `created`,o.`id`,u.`email`,u.`login`,u.`name` as `user_name`,u.`surname` ,COALESCE(h.`action_type`,0) as `action_type`, DATEDIFF( NOW() ,  MAX(COALESCE(h.`date`,o.`created`) )) AS  `exist` ,o.`owner_id`,o.`name`,o.`alias`,{$key} as `object_type`, 0 as `type`, 0 as `region`, 0 as `id_object_regions`
				FROM `{$object_type['class']}` o
				LEFT JOIN `users` u ON o.`owner_id`=u.`id`
				LEFT JOIN `objects_history` h ON h.`object_id`=o.`id` AND h.`object_type`={$key} AND h.`action_type` IN(1,2,3) /*AND h.`user_id`=o.`owner_id`*/
				WHERE o.`moderated`=1 AND o.`block`=0 AND o.`owner_id`!=30 AND u.`group`=1
				GROUP BY o.`id`
				HAVING (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))>0) AND (DATEDIFF(NOW(), MAX(COALESCE(h.`date`,o.`created`)))%{$actuality_days})=0
				ORDER BY MAX(COALESCE(h.`date`,o.`created`)) ASC";
				$this->db->query($sql);
				$result=array_merge($result,$this->db->get_result());
			}
		}
		$user_objects=array();
		global $new_region_codes;
		foreach($result as $item){
			if(!in_array($item['object_type'],array(9,12))){
				if($item['region']>0 && !in_array($item['object_type'],array(3,7,13))) $link='/'.$new_region_codes[$item['region']]['code'].'/'.$object_types[$item['object_type']]['url'].'/'.$item['alias'];
				else $link='/'.$object_types[$item['object_type']]['url'].'/'.$item['alias'];
			}elseif($item['object_type']!=12) $link='/'.$new_region_codes[$item['region']]['code'].'/'.$object_types[$item['object_type']]['url'.$item['type']].'/'.$item['alias'];
			else $link='/'.$object_types[$item['object_type']]['url'].'/';

			$user_objects[$item['owner_id']][0]=array('email'=>$item['email'],'login'=>$item['login'],'name'=>$item['user_name'],'surname'=>$item['surname']);
			if($item['object_type']==9) $item['object_type'].=$item['type'];
			$user_objects[$item['owner_id']][$item['object_type']][]=array('name'=>$item['name'],'alias'=>$item['alias'],'exist'=>$item['exist'],'link'=>$link,'type'=>$item['type'],'action_type'=>$item['action_type']);
		}
		
		if(count($user_objects)>0){
			include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
			$mailer=new Mailer();

			foreach($user_objects as $item)
			{
				$subject='';
				$msg='';
				include(__ENGINE_PATH.'/templates/email.owners.broadcast.php');
				//echo $item[0]['email'].'<br/>'.$msg.'<br/><br/><br/>';
				if(!@$mailer->mail($item[0]['email'], $subject, $msg)) {
					trigger_error('Не удалось отправить напоминания о проверке актуальности объектов',E_USER_WARNING);
				}else $mail_count++;
			}
		}
		return "отправлено писем - ".$mail_count;
	}
	
	
	/**
	 * Check media storage for avatars exist
	 *
	 */
	public function check_media_storage(){
		global $object_types;
		$sql='';
		
		foreach($object_types as $key=>&$value){
			if($key==5 || $key==6) continue;
			$sql.="SELECT {$key} as `object_type`,`id` FROM `{$value['class']}` UNION ";
		}
		unset($value);
		$sql=substr($sql,0,-7);
		$this->db->query($sql);
		$result=&$this->db->get_result();
		global $avatar_types;
		foreach($result as &$value){
			$path=__MEDIA_STORAGE_PATH.'/avatars/objects/'.$value['object_type'].'/'.$value['id'];
			if(!file_exists($path)){
				umask(0);
				if(@mkdir($path)){
					foreach($avatar_types as $key2=>&$value2){
						$path2=$path.'/'.$key2;
						if(!@mkdir($path2)) echo 'Не удалось создать '.$path2.'<br/>';
					}unset($value2);
					if($value['object_type']==4){
						$path2=$path.'/logotips';
						if(!@mkdir($path2)) echo 'Не удалось создать '.$path2.'<br/>';
					}
				}else echo 'Не удалось создать '.$path.' <br/>';
			}else {
				foreach($avatar_types as $key2=>&$value2){
					if(!file_exists($path.'/'.$key2)){
						umask(0);
						if(!@mkdir($path.'/'.$key2)) echo 'Не удалось создать '.$path.'/'.$key2.'<br/>';
					}unset($value2);
				}
			}
			
			if($value['object_type']==4 && !file_exists($path.'/logotips')){
				if(!mkdir($path.'/logotips')) echo 'Не удалось создать '.$path.'/logotips<br/>';
			}
		}
		unset ($value);
		
		$sql='SELECT u.`id` FROM `users` u';
		$this->db->query($sql);
		$result=&$this->db->get_result();
		foreach ($result as &$value) {
			$path=__MEDIA_STORAGE_PATH.'/avatars/users/'.$value['id'];
			if(!file_exists($path)){
				umask(0);
				if(@mkdir($path)){
					foreach($avatar_types as $key2=>&$value2){
						$path2=$path.'/'.$key2;
						if(!mkdir($path2)) echo 'Не удалось создать '.$path2.'<br/>';
					}unset($value2);
				}else echo 'Не удалось создать '.$path.' <br/>';
			}else {
				foreach($avatar_types as $key2=>&$value2){
					if(!file_exists($path.'/'.$key2)){
						umask(0);
						if(!@mkdir($path.'/'.$key2)) echo 'Не удалось создать '.$path.'/'.$key2.'<br/>';
					}unset($value2);
				}
			}
		}unset($value);
		
		$sql='SELECT p.`id`, p.`object_type`, p.`user_id`, p.`object_id` FROM `photoalbums` p';
		$this->db->query($sql);
		$result=&$this->db->get_result();
		global $image_types;
		foreach ($result as &$value) {
			if($value['user_id']>0)$path=__MEDIA_STORAGE_PATH.'/photos/users/'.$value['user_id'].'/'.$value['id'];
			else $path=__MEDIA_STORAGE_PATH.'/photos/objects/'.(($value['object_type']==5)?1:$value['object_type']).'/'.$value['object_id'].'/'.$value['id'];
			if(!file_exists($path)){
				umask(0);
				if(@mkdir($path)){
					foreach($image_types as $key2=>&$value2){
						if($key2==1)continue;
						$path2=$path.'/'.$key2;
						if(!@mkdir($path2)) echo 'Не удалось создать '.$path2.'<br/>';
					}unset($value2);
				}else echo 'Не удалось создать '.$path.' <br/>';
			}else {
				foreach($image_types as $key2=>&$value2){
					if($key2==1)continue;
					if(!file_exists($path.'/'.$key2)){
						umask(0);
						if(!@mkdir($path.'/'.$key2)) echo 'Не удалось создать '.$path.'/'.$key2.'<br/>';
					}
				}unset($value2);
			}
		}unset($value);
		
	}
	
	//рассылка юзерам, которые не заходили на сайт за опеределенный интервал времени
	public function email_broadcast($interval)
	{
		$sql="SELECT MAX(cv.`date`) as `maxdate`,MAX(UNIX_TIMESTAMP(cv.`date`)) as `date`,COUNT(DISTINCT e.`id`) as `events_count`,COUNT(DISTINCT m.`id`) as `message_count` ,u.`id` ,u.`login`,u.`email`  ,u.`name`, u.`surname` ";
		
		include_once(__ENGINE_PATH.'/functions/classes/class_eventer.php');
		$eventer=new Eventer($this->db);
		
		foreach($eventer->event_types as $key=>$value){
			$sql.=" ,COUNT(DISTINCT IF(e.`type`={$key},e.`id`,NULL)) as `event_type_{$key}`  ";
		}
		
		$sql.="FROM `users` u 
				INNER JOIN `visits` cv ON cv.`user_id`=u.`id` 
				LEFT JOIN `events` e ON e.`to`=u.`id` AND e.`to_readed`=0 AND  e.`created`<NOW() 
				LEFT JOIN `user_messages` m ON m.`to`=u.`id` AND m.`to_readed`=0 
				WHERE u.`group`<50
				GROUP BY u.`id` 
				HAVING DATEDIFF(NOW(),MAX( cv.`date`))>0 AND DATEDIFF(NOW(),MAX( cv.`date`))%{$interval}=0 AND (`events_count`>0 OR `message_count`>0)";
		if($this->db->query($sql))
		{
			include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
			$mailer=new Mailer();		
			
			include_once(__ENGINE_PATH.'/functions/classes/class_eventer.php');
			$eventer=new Eventer($this->db);
			
			$listeners=&$this->db->get_result();
			global $region_codes;
			$m_count=0;
			foreach($listeners as $adresat)
			{
				$user_id=$adresat['id'];
				$no_text=true;
				$subject='';
				$msg='';
				include(__ENGINE_PATH.'/functions/actions/users.reminder_all.php');
				if(count($result['important'])>0 || count($result['participation'])>0 || count($result['remind'])>0 || count($result['interesting'])>0){
					$adresat_email = $adresat['email'];
					include(__ENGINE_PATH.'/templates/email.broadcast.php');
					if(!@$mailer->mail($adresat['email'], $subject,$msg)) {
						echo ('Не удалось отправить информацию пользователям, не заходившим длительное время');
					}else {
						$m_count++;
					}
				}
			}
			return "отправлено писем - {$m_count}";
		}

		return false;
	}
	
	//рассыдка админам не заходившим на сайте $missing_days дней
	public function email_admins_broadcast($missing_days)
	{
		include_once(__ENGINE_PATH.'/functions/classes/class_eventer.php');
		$eventer=new Eventer($this->db);
		
		$sql="SELECT MAX(cv.`date`) as `maxdate`,MAX(UNIX_TIMESTAMP(cv.`date`)) as `date`,COUNT(DISTINCT e.`id`) as `events_count`,COUNT(DISTINCT m.`id`) as `message_count` ,u.`id` , ur.`region` ,u.`login`,u.`group`,u.`email` ,u.`name`, u.`surname` ";
		
		foreach($eventer->event_types as $key=>$value)
		{
			$sql.=" ,COUNT(DISTINCT IF(e.`type`={$key},e.`id`,NULL)) as `event_type_{$key}`  ";
		}
		
		$sql.="FROM `users` u 
				LEFT JOIN `users_regions` ur ON ur.`user_id`=u.`id`
				INNER JOIN `cms_visits` cv ON cv.`user_id`=u.`id` 
				LEFT JOIN `events` e ON e.`to`=u.`id` AND e.`to_readed`=0 AND e.`created`<NOW() 
				LEFT JOIN `user_messages` m ON m.`to`=u.`id` AND m.`to_readed`=0 
				WHERE u.`group`>=90
				GROUP BY u.`id` , ur.`region`  
				HAVING DATEDIFF(NOW(),MAX( cv.`date`))>0 AND DATEDIFF(NOW(),MAX( cv.`date`))%{$missing_days}=0";
		
		
		if($this->db->query($sql))
		{
			include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
			$mailer=new Mailer();
			
			$listeners=&$this->db->get_result();
			
			global $region_codes;
			$m_count=0;
			
			include_once(__ENGINE_PATH.'/cms/functions/classes/class_objects.php');
			$object_objects=new Objects();
			foreach($listeners as $adresat)
			{
				$objects=&$object_objects->get_objects_count('(o.`moderated`=0 OR (om.`id` is not NULL AND om.`created`<(NOW()-INTERVAL '.__MAX_MODIFICATION_MODERATED_HOURS.' HOUR)) OR (om2.`id` is not NULL AND om2.`created`<(NOW()-INTERVAL '.__MAX_MODIFICATION_MODERATED_HOURS.' HOUR)))',$adresat['region'],$adresat['group']);
				$objects_count=$objects[0];
			
				//$censored=&$object_objects->get_objects_count(0,0,'` DESC',array('user_id'),'o.`censored`=1',0,0,$adresat['region'],true);
				$censored_count=0;
				
				$user_id=$adresat['id'];
				$no_text=true;
				include(__ENGINE_PATH.'/functions/actions/users.reminder_all.php');
				if( $objects_count > 0 || $censored_count >0 || count($result['important'])>0 || count($result['participation'])>0 || count($result['remind'])>0 || count($result['interesting'])>0)
				{
					$subject='';
					$msg='';
					$adresat_email = '';
					include(__ENGINE_PATH.'/templates/email.broadcast.php');
					if(!@$mailer->mail($adresat['email'], $subject,$msg)) {
						trigger_error('Не удалось отправить информацию об админах, не заходивших в CMS длительное время',E_USER_WARNING);
					}
					else $m_count++;
				}
			}
			return "отправлено писем - {$m_count}";
		}

		return false;
	}
	
	public function check_missing_filial_admins($actual_after_days,$emails)
	{

		$sql="SELECT MAX(cv.`date`) as `maxdate`,MAX(UNIX_TIMESTAMP(cv.`date`)) as `date`,cv.`user_id` ,u.`login`,u.`group` ,u.`name`, u.`surname`,GROUP_CONCAT(DISTINCT ur.`region`) as `regions` 
				FROM `users` u 
				LEFT JOIN `cms_visits` cv ON cv.`user_id`=u.`id`
				LEFT JOIN `users_regions` ur ON ur.`user_id`=u.`id`
				WHERE u.`group`>=80 AND u.`group`<100 AND u.`id`!=8439 /*татьяна*/
				GROUP BY cv.`user_id` 
				HAVING `maxdate`<(NOW()-INTERVAL {$actual_after_days} DAY)";
		
		include_once(__ENGINE_PATH.'/functions/subfunctions/common.users_groups_arrays.php');
		if ($this->db->query($sql))
		{
			$admins=$this->db->get_result();
			//отправляем уведомление
			if(count($admins) > 0)
			{
				include_once(__ENGINE_PATH.'/functions/classes/class_mailer.php');
				$mailer=new Mailer();
				
				global $region_codes;
				
				$subject='';
				$msg='';
				include(__ENGINE_PATH.'/templates/email.missing_admins.php');
				
				if(!@$mailer->mail($emails, $subject,$msg)) {
					trigger_error('Не удалось отправить информацию об админах, не заходивших в CMS длительное время',E_USER_WARNING);
				}
			}
		}
	}
	
	
	//формируем таблицу последних посещений
	public function visit_last_days(){
		//обвновляем данные вспомогательной таблицы для посещений
		$sql='UPDATE `visits` SET `guest_id`=`user_id` WHERE `user_id`>0';
		$result=$this->db->query($sql);
		if(!$result) trigger_error('Не удалось обновить  `visits`',E_USER_WARNING);
		
		
		$sql='TRUNCATE TABLE `visits_last_days`';
		if(!$result || !$this->db->query($sql)) {
			$result=false;
			trigger_error('Не удалось очистить `visits_last_days`',E_USER_WARNING);
		}
		
		$last_month=gmdate("Y-m-d",gmmktime(gmdate('H'),gmdate('i'),gmdate('s'),gmdate('m')-1,gmdate('d'),gmdate('Y')));
		$sql='INSERT INTO `visits_last_days` (`last_visit`,`object_type`,`object_id`,`user_id`,`guest_id`) SELECT MAX(`date`), `object_type`, `object_id`, `user_id`, `guest_id` FROM `visits` WHERE `date`>=\''.$last_month.'\' GROUP BY `object_type`, `object_id`, `guest_id`';
		if(!$result || !$this->db->query($sql)) {
			$result=false;
			trigger_error('Не удалось сформировать `visits_last_days`',E_USER_WARNING);
		}
		
		$sql='UPDATE `visits_last_days` SET `object_type`=1 WHERE `object_type`=5';
		if(!$result || !$this->db->query($sql)) {
			$result=false;
			trigger_error('Не удалось подготовить `visits_last_days` для консультантов',E_USER_WARNING);
		}

		return $result;
	}

	public function visits_intermediate_clear()
	{
		$func_report = array('replace'=>0, 'truncate'=>0);

		$this->db->query('SELECT MAX(`id`) as `max_id` FROM `visits_intermediate`');
		$id = $this->db->get_result();

		if (isset($id[0]['max_id'])) {
			$id = $id[0]['max_id'];

			if (is_numeric($id) && $id > 0) {
				if ($this->db->query('INSERT INTO `visits` SELECT `date`,  `object_type`,  `object_id`,  `ip`,  `browser`,  `user_id`,  `guest_id`,  `ip_region` FROM `visits_intermediate` WHERE `id`<='.$id))
					$func_report['replace'] = 1;

				if ($this->db->query('DELETE FROM `visits_intermediate` WHERE `id`<='.$id))
					$func_report['truncate'] = 1;

				return 'таблица `visits` '.(!$func_report['replace'] ? 'НЕ ' : 'успешно ').'обновлена, данными из таблицы `visits_intermediate`; таблица `visits_intermediate` '.(!$func_report['truncate'] ? 'НЕ ' : 'успешно ').'очищена';
			}
			return 'неверно определен ID последней записи ('.$id.')';
		}
		return 'пустая таблица `visits_intermediate` или ошибка запроса';
	}
	
	
	//сворачиваем таблицы посещений
	public function visits_itog($visits_actual_days = 1, $visits_itog_actual_days = 30)
	{
		$func_report = array('visits_update_guestid_field'=>0, 'visits_visits_itog'=>0, 'visits_delete_old'=>0, 'visits_update_objects'=>0, 'visits_visits_interval_itog'=>0);
		//обвновляем данные вспомогательной таблицы для посещений
		$result = $this->db->query('UPDATE `visits` SET `guest_id`=`user_id` WHERE `user_id`>0 AND `user_id`!=`guest_id`');

		if ($result) {
			$func_report['visits_update_guestid_field'] = 1;
			//сворачиваем итоговые посещения
			$sql = 'REPLACE INTO `visits_itog` (`date`,`object_type`, `object_id`,`visits`, `unique_visits`)
					SELECT DATE(`date`) as mydate, `object_type`, `object_id`, COUNT(`guest_id`), COUNT(DISTINCT `guest_id`) FROM `visits`
					WHERE `date`>=DATE(NOW() - INTERVAL 1 DAY ) GROUP BY mydate, `object_id`, `object_type`';
			$result = $this->db->query($sql);

			if ($result) {
				$func_report['visits_visits_itog'] = 1;
				if ($this->db->query('DELETE FROM `visits` WHERE `date`<(NOW()-INTERVAL '.$visits_actual_days.' DAY)')
						&& $this->db->query('DELETE FROM `visits_itog` WHERE `date`<(NOW()-INTERVAL '.$visits_itog_actual_days.' DAY)')
						&& $this->db->query('DELETE FROM `cms_visits` WHERE `date`<(NOW()-INTERVAL '.$visits_itog_actual_days.' DAY)'))
					$func_report['visits_delete_old'] = 1;

				//обновляем количество посещений в объектах
				$update_obj = true;
				global $object_types;
				foreach ($object_types as $key => $value) {
					if (!in_array($key, array(5, 6))) {
						if ($key == 1) $object_type_where = 'vi.`object_type` IN(1,5)';
						else $object_type_where = 'vi.`object_type`='.$key;
						$sql="UPDATE `".$value['class']."` o SET o.`visits`=(SELECT IF(SUM(vi.`visits`) IS NOT NULL,SUM(vi.`visits`),0) FROM `visits_itog` vi WHERE vi.`object_id`=o.`id` AND {$object_type_where}),o.`unique_visits`=(SELECT IF(SUM(vi.`unique_visits`) IS NOT NULL,SUM(vi.`unique_visits`),0) FROM `visits_itog` vi WHERE vi.`object_id`=o.`id` AND {$object_type_where})";
						if (!$this->db->query($sql)) $update_obj = false;
					}
				}
				if ($update_obj) $func_report['visits_update_objects'] = 1;
				//обновляем кол-во посещений за промежуток времени
				$sql = "
					REPLACE INTO `visits_interval_itog`(`object_type`, `object_id`, `visits_last1month`, `visits_last3month`, `unique_visits_last1month`, `unique_visits_last3month`)
					(SELECT
						`object_type`,
						`object_id`,
						SUM(IF(`date` >= (DATE(NOW())-INTERVAL 1 MONTH), `visits`, 0)) as `visits_last1month`,
						SUM(IF(`date` >= (DATE(NOW())-INTERVAL 3 MONTH), `visits`, 0)) as `visits_last3month`,
						SUM(IF(`date` >= (DATE(NOW())-INTERVAL 1 MONTH), `unique_visits`, 0)) as `unique_visits_last1month`,
						SUM(IF(`date` >= (DATE(NOW())-INTERVAL 3 MONTH), `unique_visits`, 0)) as `unique_visits_last3month`
					FROM `visits_itog`
					WHERE `object_type` NOT IN (0,6)
					GROUP BY `object_type`, `object_id`
					)
				";
				if ($this->db->query($sql)) $func_report['visits_visits_interval_itog'] = 1;
			}
		}

		return 'поле `guest_id` таблицы `visits` '.(!$func_report['visits_update_guestid_field'] ? 'НЕ ' : 'успешно ').'обновлено; таблица `visits_itog` '.(!$func_report['visits_visits_itog'] ? 'НЕ ' : 'успешно ').'обновлена; таблицы `visits, `cms_visits`, `visits_itog` '.(!$func_report['visits_visits_itog'] ? 'НЕ ' : 'успешно ').'очищены от старых записей; поле `visits` в таблицах объектов '.(!$func_report['visits_visits_itog'] ? 'НЕ ' : 'успешно ').'обновлено; таблица `visits_interval_itog` '.(!$func_report['visits_visits_itog'] ? 'НЕ ' : 'успешно ').'обновлена';
	}
	
	//сворачиваем таблицы посещений баннеров
	public function banners_visits_itog($visits_actual_days,$update_now=false)
	{
		$func_report = array('banners_visits_itog'=>0, 'banner_zones_visits_itog'=>0);
		//сворачиваем итоговые посещения
		$sql='REPLACE INTO `banners_visits_itog` (`date`,`banner_id`,`visits`, `clicks`) 
			  SELECT DATE(`date`) as mydate, `banner_id`, SUM(IF(IF(`event`=1,1,0) is not null,IF(`event`=1,1,0),0)), SUM(IF(IF(`event`=2,1,0) is not null,IF(`event`=2,1,0),0)) FROM `banners_visits` 
			  WHERE DATE(`date`)=DATE(NOW()';
		if(!$update_now)$sql.=' - INTERVAL 1 DAY ';
		$sql.=') GROUP BY mydate, `banner_id`';
		
		if ($this->db->query($sql))
		{
			$sql='DELETE FROM `banners_visits` WHERE `date`<(NOW()-INTERVAL '.$visits_actual_days.' DAY)';
			$this->db->query($sql);
			$func_report['banners_visits_itog'] = 1;
		}
		
		$sql='REPLACE INTO `banner_zones_visits_itog` (`date`,`zone_id`,`visits`, `clicks`) 
			  SELECT DATE(`date`) as mydate, `zone_id`, SUM(IF(IF(`event`=1,1,0) is not null,IF(`event`=1,1,0),0)), SUM(IF(IF(`event`=2,1,0) is not null,IF(`event`=2,1,0),0)) FROM `banner_zones_visits` 
			  WHERE DATE(`date`)=DATE(NOW()';
		if(!$update_now)$sql.=' - INTERVAL 1 DAY ';
		$sql.=') GROUP BY mydate, `zone_id`';
		
		if ($this->db->query($sql))
		{
			$sql='DELETE FROM `banner_zones_visits` WHERE `date`<(NOW()-INTERVAL 14 DAY)';
			$this->db->query($sql);
			$func_report['banner_zones_visits_itog'] = 1;
		}

		return 'таблица `banners_visits_itog` '.(!$func_report['banners_visits_itog'] ? 'НЕ ' : 'успешно ').'обновлена, таблица `banner_zones_visits_itog` '.(!$func_report['banner_zones_visits_itog'] ? 'НЕ ' : 'успешно ').'обновлена';
	}
	
	//отключаем прошедшие баннеры
	public function banners_deactivate_old(){
		$sql='UPDATE `banners` SET `active`=0 WHERE `public_date_end`<now();';
		return $this->db->query($sql);
	}
	
	//очистка таблицы object_links от мертвых ссылок
	public function clear_object_links($object_types)
	{
		set_time_limit(600);
		foreach($object_types as $key=>$value)
		{
			$sql='
				SELECT l.`object1_id` as `dead_id`, COUNT(o.`id`) as `count`
				FROM `object_links` l
					LEFT JOIN `'.$value['class'].'` o ON l.`object1_id`=o.`id`
				WHERE l.`object1_type`='.$key.'
				GROUP BY `dead_id`
				HAVING `count`=0

				UNION

				SELECT l.`object2_id` as `dead_id`, COUNT(o.`id`) as `count`
				FROM `object_links` l
					LEFT JOIN `'.$value['class'].'` o ON l.`object2_id`=o.`id`
				WHERE l.`object2_type`='.$key.'
				GROUP BY `dead_id`
				HAVING `count`=0
			';
			if($this->db->query($sql))
			{
				$result=$this->db->get_result();
				if(count($result))
				{
					$sql='DELETE FROM `object_links` WHERE ';
					$i=0;
					foreach($result as $key2=>$value2)
					{
						$sql.='(`object1_type`='.$key.' AND `object1_id`='.$value2['dead_id'].') OR (`object2_type`='.$key.' AND `object2_id`='.$value2['dead_id'].') OR ';
						$i++;
						if($i==5)
						{
							$sql=substr($sql,0,-4);
							$this->db->query($sql);
							$sql='DELETE FROM `object_links` WHERE ';
							$i=0;
						}
					}
					if($i>0){
						$sql=substr($sql,0,-4);
						$this->db->query($sql);
					}
				}
			}
		}
	}
	
	//свернуть голоса у сообщений
	public function vote_message_itog()
	{
		$sql='UPDATE `messages` m SET 
		m.`vote_ok`=m.`vote_ok`+(SELECT IF(SUM(m2.`vote_ok`) is not NULL,SUM(m2.`vote_ok`),0) FROM `messages_votes` m2 WHERE m2.`message_id`=m.`id` AND m2.`vote_ok`!=0 AND m2.`date`<(NOW() - INTERVAL 6 MONTH)),
		m.`vote_bad`=m.`vote_bad`+(SELECT IF(SUM(m2.`vote_bad`) is not NULL,SUM(m2.`vote_bad`),0) FROM `messages_votes` m2 WHERE m2.`message_id`=m.`id` AND m2.`vote_bad`!=0 AND m2.`date`<(NOW() - INTERVAL 6 MONTH)),
		m.`spam`=m.`spam`+(SELECT IF(SUM(m2.`spam`) is not NULL,SUM(m2.`spam`),0) FROM `messages_votes` m2 WHERE m2.`message_id`=m.`id` AND m2.`spam`!=0 AND m2.`date`<(NOW() - INTERVAL 6 MONTH)),
		m.`vote_count`=m.`vote_count`+(SELECT COUNT(m2.`message_id`) FROM `messages_votes` m2 WHERE m2.`message_id`=m.`id` AND m2.`spam`=0 AND m2.`date`<(NOW() - INTERVAL 6 MONTH))';
		if($this->db->query($sql)) {
			$sql='DELETE FROM `messages_votes` WHERE `date`<(NOW() - INTERVAL 6 MONTH)';
			return ($this->db->query($sql));
		}
	}
	
	//свернуть голоса у отзывов
	public function vote_review_itog()
	{
		$sql='UPDATE `reviews` r SET 
		r.`vote_ok`=r.`vote_ok`+(SELECT IF(SUM(r2.`vote_ok`) is not NULL,SUM(r2.`vote_ok`),0) FROM `reviews_votes` r2 WHERE r2.`review_id`=r.`id` AND r2.`vote_ok`!=0 AND r2.`date`<(NOW() - INTERVAL 6 MONTH)),
		r.`vote_bad`=r.`vote_bad`+(SELECT IF(SUM(r2.`vote_bad`) is not NULL,SUM(r2.`vote_bad`),0) FROM `reviews_votes` r2 WHERE r2.`review_id`=r.`id` AND r2.`vote_bad`!=0 AND r2.`date`<(NOW() - INTERVAL 6 MONTH)),
		r.`vote_count`=r.`vote_count`+(SELECT COUNT(r2.`review_id`) FROM `reviews_votes` r2 WHERE r2.`review_id`=r.`id` AND r2.`date`<(NOW() - INTERVAL 6 MONTH))';
		if($this->db->query($sql)) {
			$sql='DELETE FROM `reviews_votes` WHERE `date`<(NOW() - INTERVAL 6 MONTH)';
			return ($this->db->query($sql));
		}
	}
	
	public function remove_media_storage_tempdirs()
	{
		$media_storages=array(__MEDIA_STORAGE_PATH.'/photos/temp',__MEDIA_STORAGE_PATH.'/avatars/temp',__MEDIA_STORAGE_PATH.'/banners/temp');
		foreach ($media_storages as $media_storage){
			$handle = opendir($media_storage);
			while (false !== ($photoalbum_id = readdir($handle)))
			{
				if ($photoalbum_id!=".." && $photoalbum_id!="." && is_dir($media_storage.'/'.$photoalbum_id))
				{
					if((time()-filemtime($media_storage.'/'.$photoalbum_id))>259200) 
					{
						if(!dir_remove($media_storage.'/'.$photoalbum_id)) trigger_error('Невозможно удалить временную папку '.$media_storage.'/'.$photoalbum_id,E_USER_ERROR);
					}
				}
			}
			closedir($handle);
		}
	}
	
	public function clear_old_events()
	{
		$sql='DELETE FROM `events` WHERE `created`<(NOW()-INTERVAL 6 MONTH)';
		return ($this->db->query($sql));
	}
	
	//удаляет сообщения пользователей, когда они удалили их с обоих сторон
	public function remove_user_messages()
	{
		$sql='DELETE FROM `user_messages` WHERE `user_remove`=1 AND `to_remove`=1';
		return ($this->db->query($sql));	
	}
	
	public function change_wallpaper()
	{
		$folder = __APP_PATH.'/design/background';
		$img = null;
	
		$fileList = array();
		
		$handle = @opendir($folder);
		if(!$handle){
			trigger_error('Не удалось открыть папку '.$folder,E_USER_ERROR);
			return false;
		}
		while (false !== ($file = readdir($handle))) {
			
			if(preg_match('|.+\.jpg|isu',$file) && $file != "ny2013.jpg"){
				$fileList[] = $file;
			}
		}
		closedir($handle);
		$count = count($fileList);
		if ($count > 0) {
			$imageNumber = rand(0,$count-1);
			$img = $fileList[$imageNumber];
		}

		if(in_array(gmdate("d.m.Y"), array("31.12.2012","01.01.2013","02.01.2013","03.01.2013","04.01.2013","05.01.2013","06.01.2013","14.01.2013"))) $img = "ny2013.jpg";

		//запись в файл
		$file=__ENGINE_PATH.'/templates/'.App::appPrefix().'/background.tpl';
		$fp=@fopen($file,'w');
		if($fp){
			fwrite($fp,$img);
			fclose($fp);
		}else trigger_error('Не удалось открыть для записи '.$file,E_USER_ERROR);
	}
	
	public function optimize_tables(array $tables)
	{
		if(count($tables)==0)return;
		
		foreach($tables as $item){
			 $sql="OPTIMIZE TABLE `{$item}`";
			 $this->db->query($sql);
		}
		
		foreach($tables as $item){
			 $sql="ALTER TABLE `{$item}`";
			 $this->db->query($sql);
		}
		return;
	}
	
	
	//remove dublicats from tables
	public function check_tables_dublicats(){
		$total_count=0;
		
		$sql='SELECT ol.`object_links_id` FROM `object_links` ol 
		INNER JOIN `object_links` ol2 ON 
		ol.`object_links_id`>ol2.`object_links_id` AND 
		ol.`main_link`=ol2.`main_link` AND 
		ol.`additional_link`=ol2.`additional_link` AND 
		(
			ol2.`object1_id`=ol.`object2_id` AND 
			(
				ol2.`object1_type`=ol.`object2_type` OR (ol2.`object1_type` IN(1,5) AND ol.`object2_type` IN(1,5))
			) AND 
			ol2.`object2_id`=ol.`object1_id` AND 
			(
				ol2.`object2_type`=ol.`object1_type` OR (ol2.`object2_type` IN(1,5) AND ol.`object1_type` IN(1,5))
			)
		)';
		$this->db->query($sql);
		$result=&$this->db->get_result();
		if(count($result)>0){
			$sql='DELETE FROM `object_links` WHERE ';
			foreach($result as $value) $sql.='`object_links_id`='.$value['object_links_id'].' OR ';
			$sql=substr($sql,0,-4);
			if($this->db->query($sql)) $total_count+=count($result);
		}
		
		$sql='SELECT ol.`object_links_id` FROM `object_links` ol 
		INNER JOIN `object_links` ol2 ON 
		ol.`object_links_id`>ol2.`object_links_id` AND 
		ol.`main_link`=ol2.`main_link` AND 
		ol.`additional_link`=ol2.`additional_link` AND 
		(
			ol2.`object1_id`=ol.`object1_id` AND 
			(
				ol2.`object1_type`=ol.`object1_type` OR (ol2.`object1_type` IN(1,5) AND ol.`object1_type` IN(1,5))
			) AND 
			ol2.`object2_id`=ol.`object2_id` AND 
			(
				ol2.`object2_type`=ol.`object2_type` OR (ol2.`object2_type` IN(1,5) AND ol.`object2_type` IN(1,5))
			)
		)';
		$this->db->query($sql);
		$result=&$this->db->get_result();
		if(count($result)>0){
			$sql='DELETE FROM `object_links` WHERE ';
			foreach($result as $value) $sql.='`object_links_id`='.$value['object_links_id'].' OR ';
			$sql=substr($sql,0,-4);
			if($this->db->query($sql)) $total_count+=count($result);
		}
		if($total_count>0) echo 'Удалено '.$total_count.' дубликатов из `object_links`<br/><br/>'."\n\n";
		
		
		$total_count=0;
		$sql='SELECT ol.`id_object_regions` FROM `object_regions` ol 
		INNER JOIN `object_regions` ol2 ON 
		ol.`id_object_regions`>ol2.`id_object_regions` AND 
		ol.`main_link`=ol2.`main_link` AND 
		ol.`region`=ol2.`region` AND 
		(
			ol2.`object_id`=ol.`object_id` AND 
			(
				ol2.`object_type`=ol.`object_type` OR (ol2.`object_type` IN(1,5) AND ol.`object_type` IN(1,5))
			)
		)';
		$this->db->query($sql);
		$result=&$this->db->get_result();
		if(count($result)>0){
			$sql='DELETE FROM `object_regions` WHERE ';
			foreach($result as $value) $sql.='`id_object_regions`='.$value['id_object_regions'].' OR ';
			$sql=substr($sql,0,-4);
			if($this->db->query($sql)) $total_count+=count($result);
		}
		if($total_count>0) echo 'Удалено '.$total_count.' дубликатов из `object_regions`<br/><br/>'."\n\n";
	}
	
		
	/**
	 * Rebuild levels in guidelines table
	 *
	 */
	public function rebuild_guideline_levels(){
		$updated_guidelines=0;
		$sql='SELECT g.`left_index`, g.`right_index`, g.`id` FROM `guidelines` g ORDER BY g.`left_index`';
		$this->db->query($sql);
		$result=$this->db->get_result();
		foreach($result as $key=>&$value){
			$sql='SELECT g.`level` FROM `guidelines` g WHERE g.`left_index`<'.$value['left_index'].' AND g.`right_index`>'.$value['right_index'].' LIMIT 0,1';
			$this->db->query($sql);
			$result2=$this->db->get_result();
			if(count($result2)>0) {
				$level=$result2[0]['level']+1;
				$sql='UPDATE `guidelines` SET `level`='.$level.' WHERE `id`='.$value['id'];
				if($this->db->query($sql)) $updated_guidelines++;
				else echo 'CAN NOT EXECUTE: '.$sql.'<br/>' ;
			}
		}unset($value);
		
		echo 'Всего обновлено: '.$updated_guidelines.'';
	}
	
	
	/**
	 * пересчет рейтингов для объектов у которых есть блок популярные объекты
	 *
	 * @param int $interval numder of days to count visits
	 */
	public function calculate_ratings($interval=28){
		global $object_types;
		foreach($object_types as $key=>$item){
			if($key!=3 && $key!=6 && $key!=14){
				$preffix='';
				if($key==1)$preffix='t_';
				elseif($key==5)$preffix='c_';
				$sql="	UPDATE `{$item['class']}` o SET 
				`{$preffix}rating`=(SELECT COALESCE(SUM(vi.`unique_visits`),0)
				FROM `visits_itog` vi WHERE vi.`object_type`".($key==1||$key==5?' IN(1,5)':'='.$key)." AND vi.`object_id`=o.`id` AND DATE(vi.`date`)<=DATE(NOW()) AND vi.`date`>(NOW()-INTERVAL $interval DAY))";
				$this->db->query($sql) or trigger_error("Не удалось посчитать рейтинг",E_USER_WARNING);
			}
		}
	}

	/**
	 * пересчет рейтингов для объектов у которых есть блок популярные объекты - новая формула (формула Байеса + просмотры)
	 * http://habrahabr.ru/company/darudar/blog/143188/#comment_4797256 , http://habrahabr.ru/qa/3491/
	 */
	public function calculate_ratings_baies($types){
		global $object_types;
		foreach($object_types as $key=>$item){

			if(!in_array($key, $types) || in_array($key, array(6,14))) continue;

			$preffix='';
			if($key==1)$preffix='t_';
			elseif($key==5)$preffix='c_';

			$m = 3;//минимальное кол-во голосов для участия в рейтинге
			$c = 2;
			$v = "IF(SUM(r.`value_itog`) IS NULL, 0, SUM(r.`value_itog`))";
			$r = "(SUM(r.`value_itog`)/COUNT(r.`id`))";
			$k = "(1.5/5)";

			$formula = "ROUND((IF((({$v}/({$v}+{$m}))*{$r}) IS NULL, 0, (({$v}/({$v}+{$m}))*{$r})) + ({$m}/({$v}+{$m}))*{$c})*{$k},3)";

			$sql2 = "ROUND((((o.`{$preffix}srating_itog`*o.`{$preffix}svote_itog`)/(o.`{$preffix}svote_itog`+{$m}))+(({$m}*{$c})/({$m}+o.`{$preffix}svote_itog`)))/5 ,3)";
			if($key == 2){

				$k = "(1/5)";
				$sql2 = "
					SELECT {$formula}
					FROM `object_links` ol
						JOIN `reviews` r ON r.`object_type`=3 AND r.`object_id`=ol.`object1_id`
					WHERE ol.`object2_type`=2 AND ol.`object2_id`=o.`id` AND ol.`object1_type`=3
					GROUP BY ol.`object2_id`

					UNION SELECT {$c}*{$k}
					LIMIT 1
				";
			}
			elseif($key == 4){

				$sql2 .= " +
					(SELECT {$formula}
					FROM `reviews_objects` ro
						JOIN `reviews` r ON r.`id`=ro.`review_id`
					WHERE ro.`object_type`=4 AND ro.`object_id`=o.`id`

					UNION SELECT {$c}*{$k}
					LIMIT 1)
				";
			}
			elseif($key==5){

				$sql2 .= " +
					(SELECT {$formula}
					FROM `reviews` r
					WHERE r.`object_type`=5 AND r.`object_id`=o.`id`

					UNION SELECT {$c}*{$k}
					LIMIT 1)
				";
			}
			elseif($key==1){

				$sql2 .= " +
					(SELECT {$formula}
					FROM `reviews_objects` ro
						JOIN `reviews` r ON r.`id`=ro.`review_id`
					WHERE ro.`object_type`=1 AND ro.`object_id`=o.`id`

					UNION SELECT {$c}*{$k}
					LIMIT 1)
				";
			}
			elseif($key==3){

				$sql2 .= " +
					(SELECT {$formula}
					FROM `reviews_objects` ro
						JOIN `reviews` r ON r.`id`=ro.`review_id`
					WHERE ro.`object_type`=3 AND ro.`object_id`=o.`id`

					UNION SELECT {$c}*{$k}
					LIMIT 1)
				";
			}
			elseif($key==12){
				$sql2 = "SELECT vii.`visits_last3month` FROM `visits_interval_itog` vii WHERE vii.`object_type`=12 AND vii.`object_id`=o.`id` UNION SELECT 0 LIMIT 1";
			}

			$sql="UPDATE `{$item['class']}` o SET `{$preffix}rating`=({$sql2})";
			$this->db->query($sql) or trigger_error("Не удалось посчитать рейтинг",E_USER_WARNING);
		}
	}

	/**
	 * Подсчет кол-ва оценок пользователя за мероприятие
	 * @param $types
	 */
	public function calculate_object_action_rating($types){

		global $object_types;
		foreach($types as $type){

			if(!isset($object_types[$type]) || !in_array($type, array(1,2,4))) continue;

			$tbl = $object_types[$type]['class'];

			if($type == 4){

				$sql = "
					SELECT tbl.`id` as `id`, IF(SUM(tbl.`rate`)/SUM(IF(tbl.`count`=0,0,1)) IS NULL, 0, SUM(tbl.`rate`)/SUM(IF(tbl.`count`=0,0,1))) as `rate`, SUM(tbl.`count`) as `count`
					FROM
					(
						SELECT
							ol.`object2_id` as `id`, a.`srating_itog` as `rate`, a.`svote_itog` as `count`

						FROM
							`object_links` ol
						JOIN `timetable` t ON t.`id`=ol.`object1_id` AND t.`moderated` = 1 AND t.`block` = 0
						JOIN `actions` a ON t.`action_id`=a.`id`

						WHERE
							ol.`object2_type`=4 AND ol.`object1_type`=6 AND ol.`main_link`=1

						GROUP BY
							ol.`object2_id`, a.`id`
					) as `tbl`
					GROUP BY tbl.`id`
				";
			}
			elseif($type==1){

				$sql = "
					SELECT tbl.`id` as `id`, IF(SUM(tbl.`rate`)/SUM(IF(tbl.`count`=0,0,1)) IS NULL, 0, SUM(tbl.`rate`)/SUM(IF(tbl.`count`=0,0,1))) as `rate`, SUM(tbl.`count`) as `count`
					FROM
					(
						SELECT
							ol.`object2_id` as `id`, a.`srating_itog` as `rate`, a.`svote_itog` as `count`

						FROM
							`object_links` ol
						JOIN `timetable` t ON t.`id`=ol.`object1_id` AND t.`moderated` = 1 AND t.`block` = 0
						JOIN `actions` a ON t.`action_id`=a.`id`

						WHERE
							ol.`object2_type`=1 AND ol.`object1_type`=6

						GROUP BY
							ol.`object2_id`, a.`id`
					) as `tbl`
					GROUP BY tbl.`id`
				";
			}

			if($type == 2){

				$sql2 = "
					SELECT IF(SUM(a.`srating_itog`)/SUM(IF(a.`svote_itog`=0,0,1)) IS NULL, 0, SUM(a.`srating_itog`)/SUM(IF(a.`svote_itog`=0,0,1)))
					FROM `object_links` ol
						JOIN `actions` a ON a.`id`=ol.`object1_id`
					WHERE ol.`object2_type`=2 AND ol.`object2_id`=o.`id` AND ol.`object1_type`=3
					GROUP BY ol.`object2_id`

					UNION SELECT 0

					LIMIT 1
				";

				$sql3 = "
					SELECT
						IF(SUM(a.`svote_itog`) IS NULL, 0, SUM(a.`svote_itog`))
					FROM `object_links` ol
						JOIN `actions` a ON a.`id`=ol.`object1_id`
					WHERE ol.`object2_type`=2 AND ol.`object2_id`=o.`id` AND ol.`object1_type`=3
					GROUP BY ol.`object2_id`
				";

				$sql = "UPDATE `{$tbl}` o SET `srating_itog_action`=({$sql2}), `svote_itog_action`=({$sql3})";
				$this->db->query($sql) or trigger_error("Не удалось посчитать рейтинг",E_USER_WARNING);
			}
			else{

				$sql = "
					DROP TEMPORARY TABLE IF EXISTS `temp_ratings`;

					CREATE TEMPORARY TABLE `temp_ratings`
					{$sql};

					ALTER TABLE `temp_ratings` ADD INDEX `id` (`id`);

					UPDATE `{$tbl}`, `temp_ratings` SET `{$tbl}`.`srating_itog_action` = `temp_ratings`.`rate`, `{$tbl}`.`svote_itog_action` = `temp_ratings`.`count` WHERE `{$tbl}`.`id` = `temp_ratings`.`id`;

					DROP TEMPORARY…