<?php
///////////////////////////////////////////////////////////////////////////////////////////
///Script PHP/MYSQL of management of classifieds ads developed by Script PAG
///Script PAG all rights reserved. Use under license. http://www.script-pag.com
///////////////////////////////////////////////////////////////////////////////////////////

############################################################

////////////////////////////////
//Secure random password generator
////////////////////////////////

function generate_password()
{
	$password = '';
	
	srand((double)microtime() * 1000000);
		
	$str = "abcdefghijkmnopqrstuvwxyz0123456789ABCDEFGHJKLMNOPQRSTUVWXYZ";
	$num = rand(6, 16);	
	
	for($i = 0; $i < $num; $i++) 
	$password .= $str[rand(0, 59)];

	$password = md5($password);
	$password = substr($password, 0, rand(6, 16));
	
	return $password;
}

///////////////////
//geolocation - Search ids regions and counties - autocomplete ad form map
///////////////////

function get_localisation_ids($region, $county)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_reg FROM ". PREFIX ."regions_lang WHERE (name_reg_lang LIKE :region OR name_reg_lang LIKE :rewriting_region) LIMIT 1";
	$req = $bdd->prepare($sql);
	$req->bindValue('region', "%".$region."%");
	$req->bindValue('rewriting_region', "%".rewriting($region)."%");
	$req->execute();
	$result = $req->fetch();	
	$req->closeCursor();
	
	$id_reg = !empty($result) ? $result['id_reg'] : 0;
	
	$sql = "SELECT id_county FROM ". PREFIX ."counties_lang WHERE par_reg = :id_reg AND (name_county_lang LIKE :county OR name_county_lang LIKE :rewriting_county) LIMIT 1";
	$req = $bdd->prepare($sql);
	$req->bindValue('id_reg', $id_reg);
	$req->bindValue('county', "%".$county."%");
	$req->bindValue('rewriting_county', "%".rewriting($county)."%");
	$req->execute();
	$result = $req->fetch();	
	$req->closeCursor();
		
	$id_county = !empty($result) ? $result['id_county'] : 0;
	
	return $id_reg.'-'.$id_county;
}
	
////////////////////////////////|10
//Register ad
////////////////////////////////

function register_ad($array, $password, $state, $pack_photo, $images, $files, $price_ad, $type = 'front')
{
	global $conn, $cache_search_digital, $cache_val_data, $cache_val_check, $cache_fields, $cache_visual_opts, $param_gen, $cache_photo_opt;
	
	$bdd = $conn;
	
	$ip = (isset($array['ip'])) ? $array['ip'] : $_SERVER['REMOTE_ADDR'];
	$password = password_hash($password, PASSWORD_BCRYPT, ['cost' => 8]);
	$id_county = (isset($array['county'])) ? (int) $array['county'] : 0;
	$postcode = (isset($array['postcode'])) ? $array['postcode'] : '';
	$city = (isset($array['city'])) ? $array['city'] : '';
	$phone = (isset($array['phone'])) ? $array['phone'] : '';
	$status = (int) $array['status'];
	$cat = (int) $array['cat'];
	$phone_hidden = (!empty($array['phone_hidden'])) ? $array['phone_hidden'] : 0;
	$id_acc = (!empty($array['id_acc'])) ? (int) $array['id_acc'] : 0;
	$picture_num = is_array($images) && sizeof($images) > 0 ? 1 : 0;
	$pictures_num = is_array($images) && sizeof($images) > 0 ? sizeof($images) : 0;
	$video_num = !empty($array['video']) || !empty($array['video_pack']) ? 1 : 0;
	$price = (!empty($array['price'])) ? str_replace(',' ,'.', $array['price']) : 0;
	$price = (float) $price;
	$time = time();
	$xml_key = md5(uniqid(rand(), true));
	$lat = isset($array['lat']) ? (float) $array['lat'] : 0;
	$lng = isset($array['lng']) ? (float) $array['lng'] : 0;
	
	if($type == 'front')
	{
		//Preparing options variables
	
		$top = 0;
		$top_days = 0;
		$top_time = 0;
		
		if(!empty($array['opt_type1']))
		{
			foreach($cache_visual_opts as $v)
			{
				if($v['id_visual_opt'] == $array['opt_type1'])
				{
					$top = 1;
					$top_days = (int) $v['days'];
					$top_time = time();
				}
			}
		}
		
		$premium = 0;
		$premium_days = 0;
		$premium_time = 0;
		
		if(!empty($array['opt_type2']))
		{
			foreach($cache_visual_opts as $v)
			{
				if($v['id_visual_opt'] == $_POST['opt_type2'])
				{
					$premium = 1;
					$premium_days = (int) $v['days'];
					$premium_time = time();
				}
			}
		}
		
		$urgent = 0;
		$urgent_days = 0;
		$urgent_time = 0;
		
		if(!empty($array['opt_type3']))
		{
			foreach($cache_visual_opts as $v)
			{
				if($v['id_visual_opt'] == $_POST['opt_type3'])
				{
					$urgent = 1;
					$urgent_days = (int) $v['days'];
					$urgent_time = time();
				}
			}
		}
		
		$framed = 0;
		$framed_days = 0;
		$framed_time = 0;
		
		if(!empty($array['opt_type4']))
		{
			foreach($cache_visual_opts as $v)
			{
				if($v['id_visual_opt'] == $_POST['opt_type4'])
				{
					$framed = 1;
					$framed_days = (int) $v['days'];
					$framed_time = time();
				}
			}
		}
	}
	else
	{
		//Top
		
		$top = (!empty($array['top'])) ? 1 : 0;
		$top_days = (!empty($array['top_days'])) ? $array['top_days'] : 0;
		$top_time = (!empty($array['top_time'])) ? $array['top_time'] : 0;
		
		$top_time = explode('/', $top_time);
		$top_time = mktime(0, 0, 0, $top_time[1], $top_time[0], $top_time[2]);
		
		//Premium
		
		$premium = (!empty($array['premium'])) ? 1 : 0;
		$premium_days = (!empty($array['premium_days'])) ? $array['premium_days'] : 0;
		$premium_time = (!empty($array['premium_time'])) ? $array['premium_time'] : 0;
		
		$premium_time = explode('/', $premium_time);
		$premium_time = mktime(0, 0, 0, $premium_time[1], $premium_time[0], $premium_time[2]);
		
		//Urgent
		
		$urgent = (!empty($array['urgent'])) ? 1 : 0;
		$urgent_days = (!empty($array['urgent_days'])) ? $array['urgent_days'] : 0;
		$urgent_time = (!empty($array['urgent_time'])) ? $array['urgent_time'] : 0;
		
		$urgent_time = explode('/', $urgent_time);
		$urgent_time = mktime(0, 0, 0, $urgent_time[1], $urgent_time[0], $urgent_time[2]);
		
		//Framed
		
		$framed = (!empty($array['framed'])) ? 1 : 0;
		$framed_days = (!empty($array['framed_days'])) ? $array['framed_days'] : 0;
		$framed_time = (!empty($array['framed_time'])) ? $array['framed_time'] : 0;
		
		$framed_time = explode('/', $framed_time);
		$framed_time = mktime(0, 0, 0, $framed_time[1], $framed_time[0], $framed_time[2]);
	}
	
	//Register ad
	
	$sql = "INSERT INTO ". PREFIX ."ads_search VALUES(NULL, :id_reg, :id_county, :id_cat, :postcode, :status, :type, :title, :text, :price, :state, :time, :id_acc, :picture_num, :video_num, :urgent, :premium, :lat, :lng, :comment_total, :comment_average)";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_reg', $array['reg']);
	$req->bindValue('id_county', $id_county);
	$req->bindValue('id_cat', $array['cat']);
	$req->bindValue('postcode', $postcode);
	$req->bindValue('status', $array['status']);
	$req->bindValue('type', $array['type']);
	$req->bindValue('title', replace_accents($array['title']));
	$req->bindValue('text', replace_accents($array['text']));
	$req->bindValue('price', $price);
	$req->bindValue('state', $state);
	$req->bindValue('time', $time);
	$req->bindValue('id_acc', $id_acc);
	$req->bindValue('picture_num', $picture_num);
	$req->bindValue('video_num', $video_num);
	$req->bindValue('urgent', $urgent);
	$req->bindValue('premium', $premium);
	$req->bindValue('lat', $lat);
	$req->bindValue('lng', $lng);
	$req->bindValue('comment_total', 0);
	$req->bindValue('comment_average', 0);
	
	$req->execute();
	
	$id_ad = $bdd->lastInsertId();
	
	$req->closeCursor();
	
	//Register ad info
	
	if(!empty($id_ad))
	{
		$sql = "INSERT INTO ". PREFIX ."ads VALUES(:id_ad, :title, :text, :email, :password, :city, :name, :phone, :phone_hidden, :ip, :pictures_num, :pictures_pack, :visit_num, NOW(), :xml_key, :lang)";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_ad', $id_ad);
		$req->bindValue('title', $array['title']);
		$req->bindValue('text', $array['text']);
		$req->bindValue('email', $array['email']);
		$req->bindValue('password', $password);
		$req->bindValue('city', $city);
		$req->bindValue('name', $array['name']);
		$req->bindValue('phone', $phone);
		$req->bindValue('phone_hidden', $phone_hidden);
		$req->bindValue('ip', $ip);
		$req->bindValue('pictures_num', $pictures_num);
		$req->bindValue('pictures_pack', $pack_photo);
		$req->bindValue('visit_num', 0);
		$req->bindValue('xml_key', $xml_key);
		$req->bindValue('lang', $_SESSION['code_lang']);
		$req->execute();
		$req->closeCursor();
			
		//Add pictures
		
		if(isset($images) && is_array($images) && sizeof($images) > 0)
		{
			$sql = "INSERT INTO ". PREFIX ."ads_images VALUES(NULL, :id_ad, :name) ON DUPLICATE KEY UPDATE id_ad = VALUES(id_ad)";
			$req = $bdd->prepare($sql);
			$code_lang = isset($_SESSION['code_lang']) ? $_SESSION['code_lang'] : (defined('DEFAULT_LANG') ? DEFAULT_LANG : $_SESSION['lang']);
			foreach($images as $img) {
				$img_name = array_slice(explode('/', $img), -1);
				$path_imag = implode('/', array_slice(explode('/', $img), 0, -1)); 
				$new_name = $path_imag.'/'.rewriting(name_cat($array['cat'], $code_lang).'-'.name_region($array['reg'], $code_lang).'-'.(!empty($id_county) ? name_county($id_county, $code_lang).'-' : '').$array['title'].'-').$img_name[0];
				$req->bindValue('id_ad', $id_ad);
				$req->bindValue('name', $new_name);
				$req->execute();
				if(file_exists(__DIR__.'/../upload/photos/'.$img)) {
					rename(__DIR__.'/../upload/photos/'.$img, __DIR__.'/../upload/photos/'.$new_name);
				}
				if(file_exists(__DIR__.'/../upload/thumbnails/'.$img)) {
					rename(__DIR__.'/../upload/thumbnails/'.$img, __DIR__.'/../upload/thumbnails/'.$new_name);
				}
			}
			
			$req->closeCursor();
		}
		
		//Add files
		
		if(isset($files) && is_array($files) && sizeof($files) > 0)
		{
			$sql = "INSERT INTO ". PREFIX ."ads_files VALUES(NULL, :id_ad, :name) ON DUPLICATE KEY UPDATE id_ad = VALUES(id_ad)";
			$req = $bdd->prepare($sql);
			
			foreach($files as $file) {
				$req->bindValue('id_ad', $id_ad);
				$req->bindValue('name', $file);
				$req->execute();
			}
			
			$req->closeCursor();
		}
		
		//Register compagny informations
		
		if($status == 2)
		{
			$comp_name = (isset($array['comp_name'])) ? $array['comp_name'] : '';
			$comp_num = (isset($array['comp_num'])) ? $array['comp_num'] : '';
			
			$sql = "INSERT INTO ". PREFIX ."ads_compagny VALUES(:id_ad, :comp_name, :comp_num)";
			$req = $bdd->prepare($sql);
			
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('comp_name', $comp_name);
			$req->bindValue('comp_num', $comp_num);
			$req->execute();
			$req->closeCursor();
		}
		
		//Register digital options
		
		$sql = "INSERT INTO ". PREFIX ."ads_digital VALUES(NULL, :id_ad, :id_cat_opt, :val_digital)";
		$req = $bdd->prepare($sql);
		
		foreach($cache_search_digital as $v)
		{
			$id_cat = (int) $v['id_cat'];
			$id_cat_opt_digital = (int) $v['id_cat_opt'];
			
			if($id_cat == $array['cat'] && !empty($array['digital_'. $id_cat_opt_digital]))
			{
				$req->bindValue('id_ad', $id_ad);
				$req->bindValue('id_cat_opt', $id_cat_opt_digital);
				$req->bindValue('val_digital', $array['digital_'. $id_cat_opt_digital]);
				$req->execute();
			}
		}
		
		$req->closeCursor();
		
		//Register data options
		
		$sql = "INSERT INTO ". PREFIX ."ads_data VALUES(NULL, :id_ad, :id_cat_opt, :id_val_data)";
		$req = $bdd->prepare($sql);
		
		foreach($cache_val_data as $row)
		{
			$id_val_data = (int) $row['id_val_data'];
			$id_opt_data = (int) $row['id_cat_opt'];
			
			if(!empty($array['data_'. $id_opt_data]) && $array['data_'. $id_opt_data] == $id_val_data)
			{
				$req->bindValue('id_ad', $id_ad);
				$req->bindValue('id_cat_opt', $id_opt_data);
				$req->bindValue('id_val_data', $id_val_data);
				$req->execute();
			}
		}
		
		$req->closeCursor();
		
		//Register checkbox options
		
		$sql = "INSERT INTO ". PREFIX ."ads_check VALUES(NULL, :id_ad, :id_cat_opt, :id_val_check)";
		$req = $bdd->prepare($sql);
		
		foreach($cache_val_check as $row)
		{
			$id_val_check = (int) $row['id_val_check'];
			$id_opt_check = (int) $row['id_cat_opt'];
			
			if(!empty($array['check_'. $id_val_check]) && $array['check_'. $id_val_check] == $id_val_check)
			{
				$req->bindValue('id_ad', $id_ad);
				$req->bindValue('id_cat_opt', $id_opt_check);
				$req->bindValue('id_val_check', $id_val_check);
				$req->execute();
			}
		}
		
		$req->closeCursor();
		
		//Register fields
		
		$sql = "INSERT INTO ". PREFIX ."ads_fields VALUES(NULL, :id_ad, :id_field, :value)";
		$req = $bdd->prepare($sql);
		
		foreach($cache_fields as $v)
		{
			$id_field = (int) $v['id_field'];
			
			if(!empty($array['field_'. $id_field]))
			{
				$req->bindValue('id_ad', $id_ad);
				$req->bindValue('id_field', $id_field);
				$req->bindValue('value', $array['field_'. $id_field]);
				$req->execute();
			}
		}
		
		$req->closeCursor();
		
		//Register video
		
		if(!empty($array['video']))
		{
			$video = $array['video'];
			
			preg_match('%(?:youtube(?:-nocookie)?\.com/(?:[^/]+/.+/|(?:v|e(?:mbed)?)/|.*[?&]v=)|youtu\.be/)([^"&?/ ]{11})%i', $video, $matches);
			if(isset($matches) && isset($matches[1])) $video = 'https://www.youtube.com/embed/'.$matches[1];
			
			preg_match('%(?:dailymotion\.com/(?:[^/]+/.+/|(?:v|e(?:mbed)?)/|video/|.*[?&]v=)|dai\.ly/)([^"&?/ ]*)%i', $video, $matches);
			if(isset($matches) && isset($matches[1])) $video = 'https://www.dailymotion.com/embed/video/'.$matches[1];
			
			preg_match('%(?:vimeo\.com/(?:[^/]+/.+/|(?:video/)?|.*[?&]v=))([^"&?/ ]*)%i', $video, $matches);
			if(isset($matches) && isset($matches[1])) $video = 'https://player.vimeo.com/video/'.$matches[1];
			
			preg_match("%(?:yahoo\.com/video/)%i", $video, $matches);
			if(isset($matches) && isset($matches[1])) $video .= strpos('?format=embed', $video) > -1 ? '' : '?format=embed';
			
			$sql = "INSERT INTO ". PREFIX ."ads_video VALUES(:id_ad, :video)";
			$req = $bdd->prepare($sql);
			
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('video', $video);
			$req->execute();
			$req->closeCursor();
		}
		
		//Updating account pack ads
		
		if(isset($_SESSION['connect_id']))
		{
			$member = get_info_member($_SESSION['connect_id']);
			
			$sql = "UPDATE ". PREFIX ."accounts_packs SET ads = IF(ads = 0, 0, ads - 1) WHERE id_acc = :id_acc";
			$req = $bdd->prepare($sql);
			
			$req->bindValue('id_acc', $member['id_acc']);
			$req->execute();
			$req->closeCursor();
			
			if($param_gen['auto_ads'] == 1 && $price_ad <= 0)
			{
				$sql = "UPDATE ". PREFIX ."shops SET ads_num = ads_num + 1 WHERE id_acc = :id_acc";
				$req = $bdd->prepare($sql);
				
				$req->bindValue('id_acc', $member['id_acc']);
				$req->execute();
				$req->closeCursor();
			}
		}
		
		//Insert ad calendar
		
		if(isset($_SESSION["cal_period"]))
		{
			$sql = "DELETE FROM ". PREFIX ."ads_calendar WHERE id_ad = :id_ad";
			$req = $bdd->prepare($sql);
			$req->bindValue('id_ad', $id_ad);
			$req->execute();
			
			$sql = "INSERT INTO ". PREFIX ."ads_calendar VALUES(NULL, :id_ad, :start_date, :end_date, :price)";
			$req = $bdd->prepare($sql);
			$req->bindValue('id_ad', $id_ad);
		
			foreach($_SESSION["cal_period"] as $data)
			{
				$cal_start_date = $data[0];
				$cal_start_date = substr($cal_start_date,6,4).'-'.substr($cal_start_date,3,2).'-'.substr($cal_start_date,0,2);
				$cal_start_date = strtotime($cal_start_date);
				
				$cal_end_date = $data[1];
				$cal_end_date = substr($cal_end_date,6,4).'-'.substr($cal_end_date,3,2).'-'.substr($cal_end_date,0,2);
				$cal_end_date = strtotime($cal_end_date);
				
				$cal_price = $data[2];
				
				$req->bindValue('start_date', $cal_start_date);
				$req->bindValue('end_date', $cal_end_date);
				$req->bindValue('price', $cal_price);
				$req->execute();
			}
		}
		
		//Register options
		
		if($type == 'back' || !empty($array['opt_type1']) || !empty($array['opt_type2']) || !empty($array['opt_type3']) || !empty($array['opt_type4']))
		{
			$sql = "INSERT INTO ". PREFIX ."ads_options VALUES(:id_ad, :top, :top_days, :top_time, :urgent, :urgent_days, :urgent_time, :framed, :framed_days, :framed_time, :premium, :premium_days, :premium_time)
			ON DUPLICATE KEY UPDATE top = :top, top_days = :top_days, top_time = :top_time, urgent = :urgent, urgent_days = :urgent_days, urgent_time = :urgent_time, framed = :framed, framed_days = :framed_days, framed_time = :framed_time, premium = :premium, premium_days = :premium_days, premium_time = :premium_time";
			$req = $bdd->prepare($sql);
	
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('top', $top);
			$req->bindValue('top_days', $top_days);
			$req->bindValue('top_time', $top_time);
			$req->bindValue('urgent', $urgent);
			$req->bindValue('urgent_days', $urgent_days);
			$req->bindValue('urgent_time', $urgent_time);
			$req->bindValue('framed', $framed);
			$req->bindValue('framed_days', $framed_days);
			$req->bindValue('framed_time', $framed_time);
			$req->bindValue('premium', $premium);
			$req->bindValue('premium_days', $premium_days);
			$req->bindValue('premium_time', $premium_time);
			$req->execute();
			$req->closeCursor();
			
			$sql = "UPDATE ". PREFIX ."ads_search SET urgent = :urgent, premium = :premium WHERE id_ad = :id_ad";
			$req = $bdd->prepare($sql);
			
			$req->bindValue('urgent', $urgent);
			$req->bindValue('premium', $premium);
			$req->bindValue('id_ad', $id_ad);
			$req->execute();
			$req->closeCursor();
		}
	}	

	return $id_ad;
}

////////////////////////////////
//Deleting the picture file
////////////////////////////////

function delete_upload_picture($picture)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "DELETE FROM ". PREFIX ."ads_images WHERE name = :name";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('name', $picture);
	$req->execute();
}

////////////////////////////////
//Deleting the uploader file
////////////////////////////////

function delete_upload_file($file)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "DELETE FROM ". PREFIX ."ads_files WHERE name = :name";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('name', $file);
	$req->execute();
}

///////////////////
//Confirm ad
///////////////////

function confirm_ad($id_ad, $email)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT * FROM ". PREFIX ."ads a
	LEFT JOIN ". PREFIX ."ads_search s ON a.id_ad = s.id_ad
	WHERE a.id_ad = :id_ad AND a.email = :email AND s.state = :state";
	$req = $bdd->prepare($sql);
		
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('email', $email);
	$req->bindValue('state', 0);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if(!empty($result))
	{
		$sql = "UPDATE ". PREFIX ."ads_search SET state = :state WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('state', 1);
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		$req->closeCursor();
		
		return true;
	}
	else return false;		 
}

////////////////////////////////	
//Counting the number of ads for sending the notification mail
////////////////////////////////

function count_ad_mail()
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT count(*) AS nb_ad FROM ". PREFIX ."ads_search WHERE state = :state";				  
	$req = $bdd->prepare($sql);
	
	$req->bindValue('state', 1);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	return $result['nb_ad'];
}

///////////////////
//Get the number of advertiser's ad
///////////////////

function get_nb_ads_advertiser($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT count(*) AS total 
	FROM ". PREFIX ."ads_search s
	LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad
	WHERE a.email = (SELECT email FROM ". PREFIX ."ads WHERE id_ad = :id_ad) AND s.state = :state";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('state', 2);
	
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	return $result;
}

///////////////////
//Get advertiser's ads
///////////////////

function get_all_ads_advertiser($id_ad, $offset, $limit, $sort)
{
	global $conn;
	
	$bdd = $conn;
	
	$order_req = ($sort == 2) ? " ORDER BY (s.price = 0) ASC, s.price ASC" : " ORDER BY s.date DESC";

	$sql = "SELECT s.id_ad, s.id_reg, s.id_county, s.id_cat, s.postcode, s.status, s.type, a.title, a.text, s.price, s.date, s.urgent, s.comment_total, s.comment_average, o.framed, a.name, a.city, a.visit_num, a.pictures_num, i.name AS picture_name, v.video 
	FROM ". PREFIX ."ads_search s
	LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad
	LEFT JOIN ". PREFIX ."ads_images i ON s.id_ad = i.id_ad AND i.id_ima = (SELECT MIN(id_ima) FROM ". PREFIX ."ads_images WHERE id_ad = s.id_ad)
	LEFT JOIN ". PREFIX ."ads_options o ON s.id_ad = o.id_ad
	LEFT JOIN ". PREFIX ."ads_video v ON s.id_ad = v.id_ad
	WHERE s.state = :state AND a.email = (SELECT email FROM ". PREFIX ."ads WHERE id_ad = :id_ad) ". $order_req ." LIMIT $offset, $limit";

	$req = $bdd->prepare($sql);
	
	$req->bindValue('state', 2);
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}
 
///////////////////
//Get ads from the search page
///////////////////

function get_ads_search($sql, $options, $status, $offset, $limit, $sort, $premium)
{
	global $conn;
	
	$bdd = $conn;
	
	$offset = (int) $offset;
	$limit = (int) $limit;
	$sort = (int) $sort;
	$premium = (int) $premium;
	$status = (int) $status;
	
	$condition = ($status == 1 || $status == 2) ? " AND s.status = $status" : "";
	$premium = ($premium == 1) ? " AND s.premium = 1" : "";
	$order = ($sort == 2) ? " ORDER BY s.date ASC" : (($sort == 3) ? " ORDER BY s.price ASC" : (($sort == 4) ? " ORDER BY s.price DESC" : " ORDER BY s.date DESC"));
	
	$sql = $sql . $condition . $premium . $options . $order  ." LIMIT $offset, $limit";
	$req = $bdd->prepare($sql);
	
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

///////////////////
//Get ad
///////////////////

function get_ad($id_ad, $adm)
{
	global $conn;
	
	$bdd = $conn;
	
	$condition = ($adm == 0) ? 'AND s.state IN(2, 3)' : (($adm == 2) ? 'AND s.state IN(1, 2, 3)' : '');
	
	$sql = "SELECT s.id_ad, s.id_reg, s.id_county, s.id_cat, s.postcode, s.status, s.type, s.state, a.title, a.text, s.price, s.date, s.id_acc, s.video_num, s.lat, s.lng, s.comment_total, s.comment_average, a.email, a.password, a.city, a.name, a.phone, a.phone_hidden, a.pictures_num, a.pictures_pack, a.visit_num, a.lang, DATE_FORMAT(last_visit, '%d/%m') AS last_v, DATE_FORMAT(last_visit, '%H:%i') AS last_h, sc.comp_name, sc.comp_num, o.top, o.top_days, o.top_time, o.urgent, o.urgent_days, o.urgent_time, o.framed, o.framed_days, o.framed_time, o.premium, o.premium_days, o.premium_time, v.video, i.name AS picture_name, b.id_shop, b.id_cat AS id_cat_shop, b.id_reg AS id_reg_shop, b.id_county AS id_county_shop, b.title AS title_shop, acc.type AS type_acc
	FROM ". PREFIX ."ads_search s 
	LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad
	LEFT JOIN ". PREFIX ."ads_compagny sc ON s.id_ad = sc.id_ad
	LEFT JOIN ". PREFIX ."ads_options o ON s.id_ad = o.id_ad
	LEFT JOIN ". PREFIX ."ads_images i ON s.id_ad = i.id_ad AND i.id_ima = (SELECT MIN(id_ima) FROM ". PREFIX ."ads_images WHERE id_ad = a.id_ad)
	LEFT JOIN ". PREFIX ."ads_video v ON s.id_ad = v.id_ad
	LEFT JOIN ". PREFIX ."accounts acc ON s.id_acc = acc.id_acc
	LEFT JOIN ". PREFIX ."shops b ON s.id_acc = b.id_acc OR b.id_acc = acc.id_acc
	WHERE s.id_ad = :id_ad ". $condition;
	
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if(empty($result))
	return false;
	
	else return $result;
}

///////////////////
//Get similar ads
///////////////////

function get_ads_similar($id_ad, $array, $offset, $limit)
{
	global $conn;
	
	$bdd = $conn;	
	
	$sql = "SELECT s.id_ad, s.id_reg, s.id_county, s.id_cat, a.title, s.price, s.date, i.name AS picture_name
	FROM ". PREFIX ."ads_search s 
	LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad 
	LEFT JOIN ". PREFIX ."ads_images i ON s.id_ad = i.id_ad AND i.id_ima = (SELECT MIN(id_ima) FROM ". PREFIX ."ads_images WHERE id_ad = a.id_ad)
	WHERE s.state = :state AND s.type = :type AND s.id_reg = :id_reg AND s.id_county = :id_county AND s.id_cat = :id_cat  AND s.id_ad != :id_ad
	ORDER BY s.date DESC LIMIT $offset, $limit";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('state', 2);
	$req->bindValue('type', $array['type']);
	$req->bindValue('id_reg', $array['id_reg']);
	$req->bindValue('id_county', $array['id_county']);
	$req->bindValue('id_cat', $array['id_cat']);
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

////////////////////////////////
//Update ad states
////////////////////////////////

function update_stats($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "UPDATE ". PREFIX ."ads SET visit_num = visit_num + 1, last_visit = NOW() WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
}

////////////////////////////////
//Get the files of the ad
////////////////////////////////

function get_ad_files($id_ad)
{
	global $conn;
	
	$bdd = $conn;	
	
	$sql = "SELECT name FROM ". PREFIX ."ads_files WHERE id_ad = :id_ad ORDER BY id_file";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

///////////////////
//Get ad digital options
///////////////////

function get_opts_ad_digital($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_ad, id_cat_opt, val_digital FROM ". PREFIX ."ads_digital
	WHERE id_ad = :id_ad ORDER BY id_cat_opt, id_ad_digital";
	
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result; 		
}

///////////////////
//Get ad data options
///////////////////

function get_opts_ad_data($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_ad, id_cat_opt, id_val_data FROM ". PREFIX ."ads_data
	WHERE id_ad = :id_ad ORDER BY id_cat_opt, id_ad_data";
	
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result; 		
}

///////////////////
//Get ad checkbox options
///////////////////

function get_opts_ad_check($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_ad, id_cat_opt, id_val_check FROM ". PREFIX ."ads_check
	WHERE id_ad = :id_ad ORDER BY id_cat_opt, id_ad_check";
	
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result; 		
}

///////////////////
//Get ad fields
///////////////////

function get_ad_fields($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_ad, id_field, value FROM ". PREFIX ."ads_fields WHERE id_ad = :id_ad ORDER BY id_ad_field";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

///////////////////
//Verification of the email of the ad
////////////////////

function verify_email($id_ad, $email)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT * FROM ". PREFIX ."ads a
	LEFT JOIN ". PREFIX ."ads_search s ON a.id_ad = s.id_ad
	WHERE a.id_ad = :id_ad AND a.email = :email AND s.state = :state";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('email', $email);
	$req->bindValue('state', 2);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if(empty($result))
	return false;

	else return true;
}

///////////////////
//Update the password for the ad
////////////////////

function update_password($id_ad, $email, $password)
{
	global $conn;
	
	$bdd = $conn;
	
	$password = password_hash($password, PASSWORD_BCRYPT, ['cost' => 8]);
	
	$sql = "UPDATE ". PREFIX ."ads a
	LEFT JOIN ". PREFIX ."ads_search s ON a.id_ad = s.id_ad
	SET a.password = :password WHERE a.id_ad = :id_ad AND a.email = :email AND s.state = :state";	 
	$req = $bdd->prepare($sql);

	$req->bindValue('password', $password);
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('email', $email);
	$req->bindValue('state', 2);
	$req->execute();
	$req->closeCursor();

	return true;
}

///////////////////
//Verification of the password of the ad
////////////////////

function verify_ad($id_ad, $password)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT a.password FROM ". PREFIX ."ads a
	LEFT JOIN ". PREFIX ."ads_search s ON a.id_ad = s.id_ad
	WHERE a.id_ad = :id_ad AND (s.state = :state_valid || s.state = :state_refuse)";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('state_valid', 2);
	$req->bindValue('state_refuse', 3);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if($result && password_verify($password, $result['password']))
	return true;
	
	else return false;
}

///////////////////
//Update ad
////////////////////

function update_ad($array, $id_ad, $state, $pictures_pack, $video_payed, $images = null, $files = null, $type = 'front')
{
	global $conn, $cache_search_digital, $cache_val_data, $cache_val_check, $cache_fields, $infos, $cache_photo_opt;
	
	$bdd = $conn;
	
	$email_condition = (isset($array['email']) && !empty($array['email'])) ? 'email = :email,' : '';
	$type_condition = (isset($array['type']) && !empty($array['type'])) ? 'type = :type,' : '';
	$info_id_cat = (isset($array['cat']) && !empty($array['cat'])) ? $array['cat'] : $infos['id_cat'];
	$comp_name = (isset($array['comp_name'])) ? $array['comp_name'] : '';
	$comp_num = (isset($array['comp_num'])) ? $array['comp_num'] : '';
	$id_county = (isset($array['county'])) ? (int) $array['county'] : 0;
	$postcode = (isset($array['postcode'])) ? $array['postcode'] : '';
	$status = (int) $array['status'];
	$city = (isset($array['city'])) ? $array['city'] : '';
	$phone = (isset($array['phone'])) ? $array['phone'] : '';
	$picture_num = is_array($images) && sizeof($images) > 0 ? 1 : 0;
	$pictures_num = is_array($images) && sizeof($images) > 0 ? sizeof($images) : 0;
	$video_num = !empty($video_payed) || !empty($array['video']) || !empty($array['video_pack']) ? 1 : 0;
	$phone_hidden = (!empty($array['phone_hidden'])) ? $array['phone_hidden'] : $phone_hidden = 0;
	
	if(isset($array['price']))
	{
		$price = str_replace(',' ,'.', $array['price']);
		$price = (float) $price;
	}
	else $price = 0;
	
	$lat = isset($array['lat']) ? (float) $array['lat'] : 0;
	$lng = isset($array['lng']) ? (float) $array['lng'] : 0;

	//Update ad
	
	$sql = "UPDATE ". PREFIX ."ads SET title = :title, text = :text, ". $email_condition ." city = :city, name = :name, phone = :phone, phone_hidden = :phone_hidden, pictures_num = :pictures_num, pictures_pack = :pictures_pack WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('title', $array['title']);
	$req->bindValue('text', $array['text']);
	
	if(!empty($email_condition))
	$req->bindValue('email', $array['email']);
	
	$req->bindValue('city', $city);
	$req->bindValue('name', $array['name']);
	$req->bindValue('phone', $phone);
	$req->bindValue('phone_hidden', $phone_hidden);
	$req->bindValue('pictures_num', $pictures_num);
	$req->bindValue('pictures_pack', $pictures_pack);
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
	
	$sql = "UPDATE ". PREFIX ."ads_search SET id_cat = :id_cat, id_reg = :id_reg, id_county = :id_county, ". $type_condition ." postcode = :postcode, status = :status, title = :title, text = :text, price = :price, state = :state, picture_num = :picture_num, video_num = :video_num, lat = :lat, lng = :lng WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_cat', $info_id_cat);
	$req->bindValue('id_reg', $array['reg']);
	$req->bindValue('id_county', $id_county);
	
	if(!empty($type_condition))
	$req->bindValue('type', $array['type']);
	
	$req->bindValue('postcode', $postcode);
	$req->bindValue('status', $status);
	$req->bindValue('title', replace_accents($array['title']));
	$req->bindValue('text', replace_accents($array['text']));
	$req->bindValue('price', $price);
	$req->bindValue('state', $state);
	$req->bindValue('picture_num', $picture_num);
	$req->bindValue('video_num', $video_num);
	$req->bindValue('lat', $lat);
	$req->bindValue('lng', $lng);
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
	
	//Update pictures
	
	if(isset($images) && is_array($images) && sizeof($images) > 0)
	{
		$sql = "DELETE FROM ". PREFIX ."ads_images WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		$req->closeCursor();
		
		$sql = "INSERT INTO ". PREFIX ."ads_images VALUES(NULL, :id_ad, :name) ON DUPLICATE KEY UPDATE id_ad = VALUES(id_ad)";
		$req = $bdd->prepare($sql);
		
		$code_lang = isset($_SESSION['code_lang']) ? $_SESSION['code_lang'] : (defined('DEFAULT_LANG') ? DEFAULT_LANG : $_SESSION['lang']);										 
		foreach($images as $img) {
			if(!preg_match('#^(https|http)?://#', $img) || str_replace(URL, '', $img) != $img) {
				$img = str_replace(URL.'/upload/photos/', '', $img);
				$img_name = strrpos($img, '-') > 0 ? array_slice(explode('-', $img), -1) : array_slice(explode('/', $img), -1);
				$path_imag = implode('/', array_slice(explode('/', $img), 0, -1)); 
				$new_name = $path_imag.'/'.rewriting(name_cat($info_id_cat, $code_lang).'-'.name_region($array['reg'], $code_lang).'-'.(!empty($id_county) ? name_county($id_county, $code_lang).'-' : '').$array['title']).'-'.$img_name[0];
				$new_name = substr($new_name, 0, 1) == '/' ? substr($new_name, 1) : $new_name;	
				if(file_exists(__DIR__.'/../upload/photos/'.$img)) {
					rename(__DIR__.'/../upload/photos/'.$img, __DIR__.'/../upload/photos/'.$new_name);
				}
				if(file_exists(__DIR__.'/../upload/thumbnails/'.$img)) {
					rename(__DIR__.'/../upload/thumbnails/'.$img, __DIR__.'/../upload/thumbnails/'.$new_name);
				}
			} else $new_name = $img;		 
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('name', $new_name);
			$req->execute();
		}
		
		$req->closeCursor();
	} else {
		$sql = "DELETE FROM ". PREFIX ."ads_images WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		$req->closeCursor();
	}
	
	//Update files
	
	if(isset($files) && is_array($files) && sizeof($files) > 0)
	{
		$sql = "DELETE FROM ". PREFIX ."ads_files WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		$req->closeCursor();
		
		$sql = "INSERT INTO ". PREFIX ."ads_files VALUES(NULL, :id_ad, :name) ON DUPLICATE KEY UPDATE id_ad = VALUES(id_ad)";
		$req = $bdd->prepare($sql);
		
		foreach($files as $file) {
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('name', $file);
			$req->execute();
		}
		
		$req->closeCursor();
	} else {
		$sql = "DELETE FROM ". PREFIX ."ads_files WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		$req->closeCursor();
	}
	
	//Update vidéo
	
	$video = $array['video'];
			
	preg_match('%(?:youtube(?:-nocookie)?\.com/(?:[^/]+/.+/|(?:v|e(?:mbed)?)/|.*[?&]v=)|youtu\.be/)([^"&?/ ]{11})%i', $video, $matches);
	if(isset($matches) && isset($matches[1])) $video = 'https://www.youtube.com/embed/'.$matches[1];
	
	preg_match('%(?:dailymotion\.com/(?:[^/]+/.+/|(?:v|e(?:mbed)?)/|video/|.*[?&]v=)|dai\.ly/)([^"&?/ ]*)%i', $video, $matches);
	if(isset($matches) && isset($matches[1])) $video = 'https://www.dailymotion.com/embed/video/'.$matches[1];
	
	preg_match('%(?:vimeo\.com/(?:[^/]+/.+/|(?:video/)?|.*[?&]v=))([^"&?/ ]*)%i', $video, $matches);
	if(isset($matches) && isset($matches[1])) $video = 'https://player.vimeo.com/video/'.$matches[1];
	
	preg_match("%(?:yahoo\.com/video/)%i", $video, $matches);
	if(isset($matches) && isset($matches[1])) $video .= strpos('?format=embed', $video) > -1 ? '' : '?format=embed';
	
	$sql = "INSERT INTO ". PREFIX ."ads_video VALUES(:id_ad, :video) ON DUPLICATE KEY UPDATE video = VALUES(video)";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('video', $video);
	$req->execute();
	$req->closeCursor();
	
	//Update compagny informations
	
	$sql = "DELETE FROM ". PREFIX ."ads_compagny WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
	
	if($status == 2)
	{
		$sql = "INSERT INTO ". PREFIX ."ads_compagny VALUES(:id_ad, :comp_name, :comp_num)";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_ad', $id_ad);
		$req->bindValue('comp_name', $comp_name);
		$req->bindValue('comp_num', $comp_num);
		$req->execute();
		$req->closeCursor();
	}
	
	//Update digital options
	
	$sql = "DELETE FROM ". PREFIX ."ads_digital WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
		
	$sql = "INSERT INTO ". PREFIX ."ads_digital VALUES(NULL, :id_ad, :id_cat_opt, :val_digital)";
	$req = $bdd->prepare($sql);
	
	foreach($cache_search_digital as $v)
	{
		$id_cat = (int) $v['id_cat'];
		$id_cat_opt_digital = (int) $v['id_cat_opt'];
		
		if($id_cat == $info_id_cat && !empty($array['digital_'. $id_cat_opt_digital]))
		{
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('id_cat_opt', $id_cat_opt_digital);
			$req->bindValue('val_digital', $array['digital_'. $id_cat_opt_digital]);
			$req->execute();
		}
	}
	
	$req->closeCursor();
	
	//Update data options
	
	$sql = "DELETE FROM ". PREFIX ."ads_data WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
		
	$sql = "INSERT INTO ". PREFIX ."ads_data VALUES(NULL, :id_ad, :id_cat_opt, :id_val_data)";
	$req = $bdd->prepare($sql);
	
	foreach($cache_val_data as $row)
	{
		$id_val_data = (int) $row['id_val_data'];
		$id_opt_data = (int) $row['id_cat_opt'];
		
		if(!empty($array['data_'. $id_opt_data]) && $array['data_'. $id_opt_data] == $id_val_data)
		{
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('id_cat_opt', $id_opt_data);
			$req->bindValue('id_val_data', $id_val_data);
			$req->execute();
		}
	}
	
	$req->closeCursor();
	
	//Update checkbox options
	
	$sql = "DELETE FROM ". PREFIX ."ads_check WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
		
	$sql = "INSERT INTO ". PREFIX ."ads_check VALUES(NULL, :id_ad, :id_cat_opt, :id_val_check)";
	$req = $bdd->prepare($sql);
	
	foreach($cache_val_check as $row)
	{
		$id_val_check = (int) $row['id_val_check'];
		$id_opt_check = (int) $row['id_cat_opt'];
		
		if(!empty($array['check_'. $id_val_check]) && $array['check_'. $id_val_check] == $id_val_check)
		{
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('id_cat_opt', $id_opt_check);
			$req->bindValue('id_val_check', $id_val_check);
			$req->execute();
		}
	}
	
	$req->closeCursor();
	
	//Update fields
	
	$sql = "DELETE FROM ". PREFIX ."ads_fields WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
		
	$sql = "INSERT INTO ". PREFIX ."ads_fields VALUES(NULL, :id_ad, :id_field, :value)";
	$req = $bdd->prepare($sql);
	
	foreach($cache_fields as $v)
	{
		$id_field = (int) $v['id_field'];
		
		if(!empty($array['field_'. $id_field]))
		{
			$req->bindValue('id_ad', $id_ad);
			$req->bindValue('id_field', $id_field);
			$req->bindValue('value', $array['field_'. $id_field]);
			$req->execute();
		}
	}
	
	$req->closeCursor();
	
	//Updating ad calendar
	
	if(isset($_SESSION["cal_period"]))
	{
		$sql = "DELETE FROM ". PREFIX ."ads_calendar WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		
		$sql = "INSERT INTO ". PREFIX ."ads_calendar VALUES(NULL, :id_ad, :start_date, :end_date, :price)";
		$req = $bdd->prepare($sql);
		$req->bindValue('id_ad', $id_ad);
	
		foreach($_SESSION["cal_period"] as $data)
		{
			$cal_start_date = $data[0];
			$cal_start_date = substr($cal_start_date,6,4).'-'.substr($cal_start_date,3,2).'-'.substr($cal_start_date,0,2);
			$cal_start_date = strtotime($cal_start_date);
			
			$cal_end_date = $data[1];
			$cal_end_date = substr($cal_end_date,6,4).'-'.substr($cal_end_date,3,2).'-'.substr($cal_end_date,0,2);
			$cal_end_date = strtotime($cal_end_date);
			
			$cal_price = $data[2];
			
			$req->bindValue('start_date', $cal_start_date);
			$req->bindValue('end_date', $cal_end_date);
			$req->bindValue('price', $cal_price);
			$req->execute();
		}
	}
	
	//Visual options
	
	if($type == 'back')
	{
		$time = time();
	
		//Top
		
		$top = (!empty($array['top'])) ? 1 : 0;
		$top_days = (!empty($array['top_days'])) ? $array['top_days'] : 0;
		$top_time = (!empty($array['top_time'])) ? $array['top_time'] : 0;
		
		$top_time = explode('/', $top_time);
		$top_time = mktime(0, 0, 0, $top_time[1], $top_time[0], $top_time[2]);
		
		//Premium
		
		$premium = (!empty($array['premium'])) ? 1 : 0;
		$premium_days = (!empty($array['premium_days'])) ? $array['premium_days'] : 0;
		$premium_time = (!empty($array['premium_time'])) ? $array['premium_time'] : 0;
		
		$premium_time = explode('/', $premium_time);
		$premium_time = mktime(0, 0, 0, $premium_time[1], $premium_time[0], $premium_time[2]);
		
		//Urgent
		
		$urgent = (!empty($array['urgent'])) ? 1 : 0;
		$urgent_days = (!empty($array['urgent_days'])) ? $array['urgent_days'] : 0;
		$urgent_time = (!empty($array['urgent_time'])) ? $array['urgent_time'] : 0;
		
		$urgent_time = explode('/', $urgent_time);
		$urgent_time = mktime(0, 0, 0, $urgent_time[1], $urgent_time[0], $urgent_time[2]);
		
		//Framed
		
		$framed = (!empty($array['framed'])) ? 1 : 0;
		$framed_days = (!empty($array['framed_days'])) ? $array['framed_days'] : 0;
		$framed_time = (!empty($array['framed_time'])) ? $array['framed_time'] : 0;
		
		$framed_time = explode('/', $framed_time);
		$framed_time = mktime(0, 0, 0, $framed_time[1], $framed_time[0], $framed_time[2]);
		
		$sql = "INSERT INTO ". PREFIX ."ads_options VALUES(:id_ad, :top, :top_days, :top_time, :urgent, :urgent_days, :urgent_time, :framed, :framed_days, :framed_time, :premium, :premium_days, :premium_time)
		ON DUPLICATE KEY UPDATE top = :top, top_days = :top_days, top_time = :top_time, urgent = :urgent, urgent_days = :urgent_days, urgent_time = :urgent_time, framed = :framed, framed_days = :framed_days, framed_time = :framed_time, premium = :premium, premium_days = :premium_days, premium_time = :premium_time";
		$req = $bdd->prepare($sql);
	
		$req->bindValue('id_ad', $id_ad);
		$req->bindValue('top', $top);
		$req->bindValue('top_days', $top_days);
		$req->bindValue('top_time', $top_time);
		$req->bindValue('urgent', $urgent);
		$req->bindValue('urgent_days', $urgent_days);
		$req->bindValue('urgent_time', $urgent_time);
		$req->bindValue('framed', $framed);
		$req->bindValue('framed_days', $framed_days);
		$req->bindValue('framed_time', $framed_time);
		$req->bindValue('premium', $premium);
		$req->bindValue('premium_days', $premium_days);
		$req->bindValue('premium_time', $premium_time);
		$req->execute();
		$req->closeCursor();
		
		$sql = "UPDATE ". PREFIX ."ads_search SET urgent = :urgent, premium = :premium WHERE id_ad = :id_ad";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('urgent', $urgent);
		$req->bindValue('premium', $premium);
		$req->bindValue('id_ad', $id_ad);
		$req->execute();
		$req->closeCursor();
	}
	
	return true;
}

///////////////////
//Delete ad
////////////////////

function delete_ad($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "UPDATE ". PREFIX ."ads_search SET state = :state WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);

	$req->bindValue('state', 6);
	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
	
	if(isset($_SESSION['connect_id']))
	{
		$sql = "UPDATE ". PREFIX ."shops SET ads_num = ads_num - 1 WHERE id_acc = :id_acc";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_acc', $_SESSION['connect_id']);
		$req->execute();
		$req->closeCursor();
	}

	return true;
}

///////////////////
//Delete pictures from an ad
////////////////////

function delete_ad_images($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$images = get_ad_pictures($id_ad);

	if(is_array($images))
	{
		foreach ($images as $image_name)
		{
			if(preg_match('#^https?://#', $image_name['name']) == false)
			{
				$del = "upload/photos/". $image_name['name'];
				unlink($del);
			
				$del = "upload/thumbnails/". $image_name['name'];
				unlink($del);
			}
		}
	}
	
	$sql = "DELETE FROM ". PREFIX ."ads_images WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);

	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	$req->closeCursor();
	
	return true;
}

////////////////////////////////
//Checking a duplicate email address
///////////////////////////////////

function check_email($email)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT email FROM ". PREFIX ."accounts WHERE email = :email";
	$req = $bdd->prepare($sql);

	$req->bindValue('email', $email);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();	
	
	if(empty($result))
	return false;
 
	else return true;
}

////////////////////////////////
//Create member or PRO account
///////////////////////////////////

function create_account($array, $type, $state)
{
	global $conn;
	
	$bdd = $conn;
	
	$type = (int) $type;
	
	$id_county = (isset($array['county'])) ? (int) $array['county'] : 0;
	$id_cat = (isset($array['cat'])) ? (int) $array['cat'] : 0;
	$comp_name = (isset($array['comp_name'])) ? $array['comp_name'] : '';
	$comp_num = (isset($array['comp_num'])) ? $array['comp_num'] : '';
	$postcode = (isset($array['postcode'])) ? $array['postcode'] : '';
	$city = (isset($array['city'])) ? $array['city'] : '';
	$phone = (isset($array['phone'])) ? $array['phone'] : '';
	$pseudo = (isset($array['pseudo'])) ? $array['pseudo'] : '';
	$pseudo_display = (isset($array['pseudo_display'])) ? (int) $array['pseudo_display'] : 0;
	$password = password_hash($array['pas'], PASSWORD_BCRYPT, ['cost' => 8]);
	
	$sql = "INSERT INTO ". PREFIX ."accounts VALUES(NULL, :id_reg, :id_county, :id_cat, :comp_name, :comp_num, :civility, :name, :first_name, :pseudo, :pseudo_display, :address, :postcode, :city, :phone, :email, :password, :ip, NOW(), :type, :state, :lang, :facebook_id)";
	$req = $bdd->prepare($sql);

	$req->bindValue('id_reg', $array['reg']);
	$req->bindValue('id_county', $id_county);
	$req->bindValue('id_cat', $id_cat);
	$req->bindValue('comp_name', $comp_name);
	$req->bindValue('comp_num', $comp_num);
	$req->bindValue('civility', $array['civility']);
	$req->bindValue('name', $array['name']);
	$req->bindValue('first_name', $array['first_name']);
	$req->bindValue('pseudo', $pseudo);
	$req->bindValue('pseudo_display', $pseudo_display);
	$req->bindValue('address', $array['address']);
	$req->bindValue('postcode', $postcode);
	$req->bindValue('city', $city);
	$req->bindValue('phone', $phone);
	$req->bindValue('email', $array['email']);
	$req->bindValue('password', $password);
	$req->bindValue('ip', $array['ip']);
	$req->bindValue('type', $type);
	$req->bindValue('state', $state);
	$req->bindValue('lang', $_SESSION['code_lang']);
	$req->bindValue('facebook_id', 0);
	$req->execute();
	
	$id_acc = $bdd->lastInsertId();
	$req->closeCursor();
	
	return $id_acc;
}

////////////////////////////////
//Update ads from the created account
///////////////////////////////////

function update_ads_account($email, $id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "UPDATE ". PREFIX ."ads_search s
	LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad 
	SET id_acc = :id_acc WHERE a.email = :email";
	$req = $bdd->prepare($sql);

	$req->bindValue('id_acc', $id_acc);
	$req->bindValue('email', $email);
	
	$req->execute();
	$req->closeCursor();
}

////////////////////////////////
//Managing the connection with Facebook
///////////////////////////////////

function facebook_conn($array, $type)
{
	global $conn;
	
	$bdd = $conn;
	
	$type = (int) $type;
	
	$email = htmlspecialchars($array['email']);
	$facebook_id = htmlspecialchars($array['id']);
	$first_name = (!empty($array['first_name'])) ? htmlspecialchars($array['first_name']) : '';
	$last_name = (!empty($array['last_name'])) ? htmlspecialchars($array['last_name']) : '';
	
	$sql = "SELECT id_acc, comp_name, address, email, facebook_id FROM ". PREFIX ."accounts WHERE email = :email OR facebook_id = :facebook_id";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('email', $email);
	$req->bindValue('facebook_id', $facebook_id);
	$req->execute();
	
	$result = $req->fetch();
	$req->closeCursor();
	
	if((empty($result['email']) || empty($result['facebook_id'])) && $type == 0)
	{
		$r['url'] = 4;
		return $r;
	}
	elseif(!empty($result['email']) || !empty($result['facebook_id']))
	{
		if(!empty($result['address']))
		$r['url'] = 1;
		
		else $r['url'] = 2;
		
		$r['id_acc'] = $result['id_acc'];
	}
	elseif(empty($email) && empty($facebook_id))
	{
		$r['url'] = 3;
	}
	else
	{
		$r['url'] = 2;
		
		$password = generate_password();
		$password = password_hash($array['password'], PASSWORD_BCRYPT, ['cost' => 8]);
		$ip = $_SERVER['REMOTE_ADDR'];
	
		$sql = "INSERT INTO ". PREFIX ."accounts VALUES(NULL, :id_reg, :id_county, :id_cat, :comp_name, :comp_num, :civility, :name, :first_name, :pseudo, :pseudo_display, :address, :postcode, :city, :phone, :email, :password, :ip, NOW(), :type, :state, :lang, :facebook_id)";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('id_reg', 0);
		$req->bindValue('id_county', 0);
		$req->bindValue('id_cat', 0);
		$req->bindValue('comp_name', '');
		$req->bindValue('comp_num', '');
		$req->bindValue('civility', 'M');
		$req->bindValue('name', $last_name);
		$req->bindValue('first_name', $first_name);
		$req->bindValue('pseudo', '');
		$req->bindValue('pseudo_display', 0);
		$req->bindValue('address', '');
		$req->bindValue('postcode', '');
		$req->bindValue('city', '');
		$req->bindValue('phone', '');
		$req->bindValue('email', $email);
		$req->bindValue('password', $password);
		$req->bindValue('ip', $ip);
		$req->bindValue('type', $type);
		$req->bindValue('state', 2);
		$req->bindValue('lang', $_SESSION['code_lang']);
		$req->bindValue('facebook_id', $facebook_id);
		$req->execute();
		
		$id_acc = $bdd->lastInsertId();
		
		$r['id_acc'] = $id_acc;
	}
	
	return $r;
}

///////////////////
//Confirm account
///////////////////

function confirm_acc($email, $type)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_acc FROM ". PREFIX ."accounts WHERE email = :email AND state = :state AND type = :type";
	$req = $bdd->prepare($sql);

	$req->bindValue('email', $email);
	$req->bindValue('state', 0);
	$req->bindValue('type', $type);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if(!empty($result))
	{
		$sql = "UPDATE ". PREFIX ."accounts SET state = :state WHERE email = :email AND type = :type";
		$req = $bdd->prepare($sql);

		$req->bindValue('state', 1);
		$req->bindValue('email', $email);
		$req->bindValue('type', $type);
		$req->execute();
		$req->closeCursor();
		
		return true;
	}
	else return false;	 
}

////////////////////////////////	
//Counting the number of accounts for sending the notification mail
////////////////////////////////

function count_acc_mail()
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT count(*) AS nb_acc FROM ". PREFIX ."accounts WHERE state = :state";				  
	$req = $bdd->prepare($sql);

	$req->bindValue('state', 1);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	return $result['nb_acc'];
}

///////////////////
//Check account for login
///////////////////

function check_account($email, $password)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_acc, password FROM ". PREFIX ."accounts WHERE state = :state AND email = :email";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('state', 2);
	$req->bindValue('email', $email);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if($result && password_verify($password, $result['password']))
	return $result;
	
	else return false;
}

///////////////////
//Check account for sending a password
///////////////////

function check_acc_password($email)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT COUNT(*) AS nb_acc FROM ". PREFIX ."accounts WHERE state = :state AND email = :email";
	$req = $bdd->prepare($sql);

	$req->bindValue('state', 2);
	$req->bindValue('email', $email);
	$req->execute();
	
	$reponse = $req->fetch();
	
	$req->closeCursor();
	
	if($reponse['nb_acc'] > 0)
	return true;
	
	else return false;
}

///////////////////
//Update password
///////////////////

function update_acc_passord($email, $password)
{
	global $conn;
	
	$bdd = $conn;
	
	$password = password_hash($password, PASSWORD_BCRYPT, ['cost' => 8]);
	
	$sql = "UPDATE ". PREFIX ."accounts SET password = :password WHERE state = :state AND email = :email";	 
	$req = $bdd->prepare($sql);

	$req->bindValue('email', $email);
	$req->bindValue('state', 2);
	$req->bindValue('password', $password);
	$req->execute();
	$req->closeCursor();

	return true;
}

///////////////////
//Get acc credit
///////////////////

function get_acc_credit($id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT credits FROM ". PREFIX ."accounts_credit WHERE id_acc = :id_acc";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	return $result && isset($result['credits']) ? $result['credits'] : 0;
}

///////////////////
//Update acc credit
///////////////////

function update_acc_credit($id_acc, $credits)
{
	global $param_gen, $conn;
	
	$bdd = $conn;
	
	$time = time();
	
	$sql = "INSERT INTO ". PREFIX ."accounts_credit VALUES(NULL, :id_acc, :credits, :time) ON DUPLICATE KEY UPDATE credits = VALUES(credits), time = VALUES(time)";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_acc', $id_acc);
	$req->bindValue('credits', $credits);
	$req->bindValue('time', $time);
		
	$req->execute();
	$req->closeCursor();
	
	return true;
}

///////////////////
//Get number of ad of the dashboard search page
///////////////////

function get_nb_ads_dashboard($sql, $id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$id_acc = (int) $id_acc;
	
	$sql = str_replace('req_count', 'AND s.id_acc = :id_acc', $sql);
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_acc', $id_acc);
	
	$req->execute();
	
	$nb_search = $req->fetchAll();
	
	$req->closeCursor();
	
	$sql = "SELECT COUNT(*) AS global FROM ". PREFIX ."ads_search s
	LEFT JOIN ". PREFIX ."ads a ON a.id_ad = s.id_ad
	WHERE state = 2 AND s.id_acc = :id_acc";				
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_acc', $id_acc);
	
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	$result = array('total_par' => $nb_search[0]['count_par'], 'total_pro' => $nb_search[0]['count_pro'], 'global' => $result['global']);
	return $result;
}

///////////////////
//Get ads from the dashboard search page
///////////////////

function get_search_ads_dashboard($sql, $id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$id_acc = (int) $id_acc;
	
	$sql = $sql ." AND s.id_acc = :id_acc ORDER BY s.date DESC"; 
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_acc', $id_acc);
	
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

////////////////////////////////
//Getting invoices of the connected member
///////////////////////////////////

function get_invoices($id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT id_inv, id_acc, id_ad, url, price, price_vat, created FROM ". PREFIX ."invoices WHERE id_acc = :id_acc ORDER BY id_inv";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	if(empty($result))
	return false;
 
	else return $result;
}

////////////////////////////////
//Checking a duplicate email address
///////////////////////////////////

function check_email_acc($email, $id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT email FROM ". PREFIX ."accounts WHERE email = :email AND id_acc != :id_acc";
	$req = $bdd->prepare($sql);

	$req->bindValue('email', $email);
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();	
	
	if(empty($result))
	return false;
 
	else return true;
}

///////////////////
//Update account
///////////////////

function update_account($array, $id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$id_county = (isset($array['county'])) ? $array['county'] : 0;
	$id_cat = (!empty($array['cat'])) ? $array['cat'] : 0;
	$postcode = (isset($array['postcode'])) ? $array['postcode'] : '';
	$city = (isset($array['city'])) ? $array['city'] : '';
	$phone = (isset($array['phone'])) ? $array['phone'] : '';
	$comp_name = (isset($array['comp_name'])) ? $array['comp_name'] : '';
	$comp_num = (isset($array['comp_num'])) ? $array['comp_num'] : '';
	$pseudo = (isset($array['pseudo'])) ? $array['pseudo'] : '';
	$pseudo_display = (!empty($array['pseudo_display'])) ? $array['pseudo_display'] : 0;
	
	$sql_id_cat = (!empty($id_cat)) ? ' id_cat = :id_cat,' : '';
	$sql_comp_name = (isset($array['comp_name'])) ? ' comp_name = :comp_name,' : '';
	$sql_comp_num = (isset($array['comp_num'])) ? ' comp_num = :comp_num,' : '';
	
	if(!empty($array['new_pas']))
	{
		$password = password_hash($array['new_pas'], PASSWORD_BCRYPT, ['cost' => 8]);
		$condition = "password = :password,";
	}
	else $condition = "";
	
	$sql = "UPDATE ". PREFIX ."accounts SET id_reg = :id_reg, id_county = :id_county, ". $sql_id_cat . $sql_comp_name . $sql_comp_num ." civility = :civility, name = :name, first_name = :first_name, pseudo = :pseudo, pseudo_display = :pseudo_display, address = :address, postcode = :postcode, city = :city, phone = :phone, email = :email,  ". $condition ." ip = :ip WHERE id_acc = :id_acc";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_reg', $array['reg']);
	$req->bindValue('id_county', $id_county);
	
	if(!empty($id_cat))
	$req->bindValue('id_cat', $id_cat);
	
	if(!empty($comp_name))
	$req->bindValue('comp_name', $comp_name);
	
	if(!empty($comp_num))
	$req->bindValue('comp_num', $comp_num);
	
	$req->bindValue('civility', $array['civility']);
	$req->bindValue('name', $array['name']);
	$req->bindValue('first_name', $array['first_name']);
	$req->bindValue('pseudo', $pseudo);
	$req->bindValue('pseudo_display', $pseudo_display);
	$req->bindValue('address', $array['address']);
	$req->bindValue('postcode', $postcode);
	$req->bindValue('city', $city);
	$req->bindValue('email', $array['email']);
	$req->bindValue('phone', $phone);
	
	if(!empty($array['new_pas']))
	$req->bindValue('password', $password);
	
	$req->bindValue('ip', $array['ip']);
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	$req->closeCursor();
	
	//Update shop
	
	$sql = "UPDATE ". PREFIX ."shops SET id_reg = :id_reg, ". $sql_id_cat ." id_county = :id_county WHERE id_acc = :id_acc";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_reg', $array['reg']);
	
	if(!empty($id_cat))
	$req->bindValue('id_cat', $id_cat);
	
	$req->bindValue('id_county', $id_county);
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	$req->closeCursor();
	
	//Update ads name
	
	if(!empty($comp_name))
	{
		$sql = "UPDATE ". PREFIX ."ads_compagny c
		LEFT JOIN ". PREFIX ."ads_search s ON c.id_ad = s.id_ad
		SET c.comp_name = :comp_name WHERE s.id_acc = :id_acc";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('comp_name', $comp_name);
		$req->bindValue('id_acc', $id_acc);
		$req->execute();
		$req->closeCursor();
	}
	else
	{
		$name = $pseudo_display == 1 ? $pseudo : $array['name'];

		$sql = "UPDATE ". PREFIX ."ads a
		LEFT JOIN ". PREFIX ."ads_search s ON a.id_ad = s.id_ad
		SET a.name = :name WHERE s.id_acc = :id_acc";
		$req = $bdd->prepare($sql);
		
		$req->bindValue('name', $name);
		$req->bindValue('id_acc', $id_acc);
		$req->execute();
		$req->closeCursor();
	}
	
	return true;
}

///////////////////
//Save account search
///////////////////

function save_account_search($id_acc, $url) {
	global $conn, $visit_latitude, $visit_longitude;
	$bdd = $conn;
	$sql = "INSERT INTO ".PREFIX."accounts_search VALUES(NULL, :id_acc, :search, :visit_latitude, :visit_longitude, :send_mail) ON DUPLICATE KEY UPDATE send_mail = VALUES(send_mail)";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_acc', $id_acc);
	$req->bindValue('search', $url);
	$req->bindValue('visit_latitude', $visit_latitude);
	$req->bindValue('visit_longitude', $visit_longitude);
	$req->bindValue('send_mail', 0);
	$result = $req->execute();
	$req->closeCursor();
	return $result;
}

///////////////////
//Update account search send mail field
///////////////////

function update_account_search_send_mail($id_search, $value) {
	global $conn;
	$bdd = $conn;
	$sql = "UPDATE ".PREFIX."accounts_search SET send_mail = :send_mail WHERE id_account_search = :id_search";
	$req = $bdd->prepare($sql);	
	$req->bindValue('send_mail', $value);
	$req->bindValue('id_search', $id_search);
	$result = $req->execute();
	$req->closeCursor();
	return $result;
}

///////////////////
//Delete account search
///////////////////

function delete_account_search($id_search) {
	global $conn;
	$bdd = $conn;
	$sql = "DELETE FROM ".PREFIX."accounts_search WHERE id_account_search = :id_search";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_search', $id_search);
	$result = $req->execute();
	$req->closeCursor();
	return $result;
}

///////////////////
//test if the search is already saved for this user account
///////////////////

function is_acc_search($id_acc, $query) {
	global $conn;
	$bdd = $conn;
	$sql = "SELECT id_account_search FROM ".PREFIX."accounts_search WHERE id_acc = :id_acc AND search = :search";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_acc', $id_acc);
	$req->bindValue('search', $query);
	$req->execute();
	$result = $req->fetch();
	$req->closeCursor();
	return $result && $result['id_account_search'] ? true : false;
}

///////////////////
//Get all account searches
///////////////////

function get_acc_searches($id_acc) {
	global $conn;
	$bdd = $conn;
	$sql = "SELECT * FROM ".PREFIX."accounts_search WHERE id_acc = :id_acc";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	$result = $req->fetchAll();
	$req->closeCursor();
	return $result ? $result : 0;
}

///////////////////
//Get account searches
///////////////////

function get_acc_search($search) {
	global $conn;
	$bdd = $conn;
	$sql = "SELECT * FROM ".PREFIX."accounts_search WHERE search = :search";
	$req = $bdd->prepare($sql);	
	$req->bindValue('search', $search);
	$req->execute();
	$result = $req->fetch();
	$req->closeCursor();
	return $result ? $result : 0;
}

///////////////////
//Count account search
///////////////////

function get_acc_search_count($id_acc) {
	global $conn;
	$bdd = $conn;
	$sql = "SELECT COUNT(id_account_search) as count FROM ".PREFIX."accounts_search WHERE id_acc = :id_acc";
	$req = $bdd->prepare($sql);	
	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	$result = $req->fetchAll();
	$req->closeCursor();
	return $result && isset($result[0]) && isset($result[0]['count']) ? $result[0]['count'] : 0;
}

///////////////////
//Insert the mail filter
///////////////////

function insert_send_message($array, $id_ad, $email_advertiser)
{
	global $conn;
	
	$bdd = $conn;
	
	$date = time();
	
	$name = $array['msg_name'];
	$email = $array['msg_email'];
	$phone = $array['msg_phone'];
	$message = $array['msg_msg'];
	$ip = $_SERVER['REMOTE_ADDR'];
	
	$sql = "INSERT INTO ". PREFIX ."mail_filter VALUES(NULL, :id_ad, :name, :email, :phone, :email_advertiser, :message, :ip, :date, :state)";
	$req = $bdd->prepare($sql);
	
	$req->bindValue('id_ad', $id_ad);
	$req->bindValue('name', $name);
	$req->bindValue('email', $email);
	$req->bindValue('phone', $phone);
	$req->bindValue('email_advertiser', $email_advertiser);
	$req->bindValue('message', $message);
	$req->bindValue('ip', $ip);
	$req->bindValue('date', $date);
	$req->bindValue('state', 0);
	$req->execute();
	$req->closeCursor();
	
	return true;		 
}

////////////////////////////////	
//Count the number of mail to send the notification mail
////////////////////////////////

function count_mail_mail()
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT count(*) AS nb_mail FROM ". PREFIX ."mail_filter WHERE state = :state";			  
	$req = $bdd->prepare($sql);
	
	$req->bindValue('state', 0);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	$nb_mail = $result['nb_mail'];
	
	return $nb_mail;
}

///////////////////
//Search phone of the ad
///////////////////

function get_phone_ad($id_ad)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT phone FROM ". PREFIX ."ads WHERE id_ad = :id_ad";
	$req = $bdd->prepare($sql);

	$req->bindValue('id_ad', $id_ad);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if(count($result) <= 0)		
	return 0;
	
	else return $result['phone'];
}

///////////////////
//Search phone of the shop
///////////////////

function get_phone_shop($id_acc)
{
	global $conn;
	
	$bdd = $conn;
	
	$sql = "SELECT phone FROM ". PREFIX ."accounts WHERE id_acc = :id_acc";
	$req = $bdd->prepare($sql);

	$req->bindValue('id_acc', $id_acc);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	if(count($result) <= 0)		
	return 0;
	
	else return $result['phone'];
}

///////////////////
//Get ad calendar
////////////////////

function get_calendar($id)
{
	global $conn;
	
	$bdd = $conn;

	$sql = "SELECT * FROM ". PREFIX ."ads_calendar WHERE id_ad = :id_ad ORDER BY start_date";
	$req = $bdd->prepare($sql);
		
	$req->bindValue('id_ad', $id);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

///////////////////
//Get ad comments
////////////////////

function get_ad_comments($id)
{
	global $conn;
	
	$bdd = $conn;

	$sql = "SELECT * FROM ". PREFIX ."ads_comments WHERE id_ad = :id_ad AND state = :state ORDER BY date DESC";
	$req = $bdd->prepare($sql);
		
	$req->bindValue('id_ad', $id);
	$req->bindValue('state', 2);
	$req->execute();
	
	$result = $req->fetchAll();
	
	$req->closeCursor();
	
	return $result;
}

////////////////////////////////	
//Counting the number of comments for sending the notification mail
////////////////////////////////

function count_comment_mail($comment_type)
{
	global $conn;
	
	$bdd = $conn;
	
	if($comment_type == 1)
	$sql = "SELECT count(*) AS nb_comment FROM ". PREFIX ."ads_comments WHERE state = :state";	
	
	elseif($comment_type == 2)
	$sql = "SELECT count(*) AS nb_comment FROM ". PREFIX ."shops_comments WHERE state = :state";
				  
	$req = $bdd->prepare($sql);

	$req->bindValue('state', 1);
	$req->execute();
	
	$result = $req->fetch();
	
	$req->closeCursor();
	
	return $result['nb_comment'];
}