'SET NAMES utf8'); // Transactions UFT8 @$bdd = new PDO('mysql:host='. HOSTNAME .';dbname='. DB_NAME, DB_USERNAME, DB_PASSWORD, $set_utf8); $bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); } catch(Exception $error) { die(''. $language['error_bdd_connect'] .''); } return $bdd; } //////////////////////////////// //Get the pictures of the ad //////////////////////////////// function get_ad_pictures($id_ad) { global $conn; $bdd = $conn; $sql = "SELECT name FROM ". PREFIX ."ads_images WHERE id_ad = :id_ad ORDER BY id_ima"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $result = $req->fetchAll(); $req->closeCursor(); return $result; } //////////////////////////////// //Caching the number of ads //////////////////////////////// function get_nb_ads() { global $conn, $cache_regions; $bdd = $conn; $sql = "SELECT count(*) AS total FROM ". PREFIX ."ads_search WHERE state = :state"; $req = $bdd->prepare($sql); $req->bindValue('state', 2); $req->execute(); $result = $req->fetch(); $req->closeCursor(); $total = $result['total']; $cache_nb_ads = ' \''. $total .'\','. "\n" .''; foreach($cache_regions as $reg) { $sql = "SELECT count(*) AS total FROM ". PREFIX ."ads_search WHERE state = :state AND id_reg = :id_reg"; $req = $bdd->prepare($sql); $req->bindValue('state', 2); $req->bindValue('id_reg', $reg['id_reg']); $req->execute(); $result = $req->fetch(); $req->closeCursor(); $total = $result['total']; $cache_nb_ads .= '\'reg_'. $reg['id_reg'] .'\' => \''. $total .'\','. "\n" .''; } $cache_nb_ads .= ');'; file_put_contents(__DIR__.'/../includes/cache/cache_nb_ads.php', $cache_nb_ads); if(function_exists('opcache_invalidate')) opcache_invalidate(__DIR__.'/../includes/cache/cache_nb_ads.php'); return true; } /////////////////// //Get the ad number of the search page /////////////////// function get_nb_ads_search($sql, $options) { global $conn; $bdd = $conn; $sql = str_replace('req_count', $options, $sql); $req = $bdd->prepare($sql); $req->execute(); $result = $req->fetch(); $req->closeCursor(); return $result; } //////////////////////////////// //Get member information /////////////////////////////////// function get_info_member($id_acc) { global $conn; $bdd = $conn; $sql = "SELECT c.id_acc, c.id_reg, c.id_county, c.id_cat, c.comp_name, c.comp_num, c.civility, c.name, c.first_name, c.pseudo, c.pseudo_display, c.address, c.postcode, c.city, c.phone, c.email, c.type, c.lang, p.id_acc AS pack, p.days as nb_days, p.ads as nb_ads, p.limit_1, p.limit_2, p.time, pv.id_acc as pack_shop, pv.days as days_pack_shop, pv.limit_1 as limit_pack_shop, pv.time as time_pack_shop, i.id_acc as invoice FROM ". PREFIX ."accounts c LEFT JOIN ". PREFIX ."accounts_packs p ON c.id_acc = p.id_acc LEFT JOIN ". PREFIX ."shops_packs pv ON c.id_acc = pv.id_acc LEFT JOIN ". PREFIX ."invoices i ON c.id_acc = i.id_acc WHERE c.state IN(2, 3) AND c.id_acc = :id_acc"; $req = $bdd->prepare($sql); $req->bindValue('id_acc', $id_acc); $req->execute(); $result = $req->fetch(); $req->closeCursor(); if(empty($result)) return false; else return $result; } /////////////////// //Get the result of the search /////////////////// function make_sql($array, $array_keywords, $visit_latitude = 0, $visit_longitude = 0, $distance = 0) { global $conn, $language, $param_gen; $bdd = $conn; $postcode = (!empty($array['postcode']) && !is_array($array['postcode']) AND $array['postcode'] != $language['value_postcode']) ? $bdd->quote($array['postcode']) : 0; $reg = (!empty($array['reg'])) ? (int) $array['reg'] : 0; $county = (!empty($array['county'])) ? (int) $array['county'] : 0; $cat = (!empty($array['cat']) && !is_array($array['cat'])) ? htmlspecialchars($array['cat']) : 0; $cat_sql = (!empty($array['cat'])) ? (int) $array['cat'] : 0; $type = (!empty($array['type']) && $array['type'] > 0 && $array['type'] < 5) ? (int) $array['type'] : 1; $condition = " s.state = 2"; $condition .= " AND s.type = ". $type; $condition .= (!empty($postcode)) ? " AND s.postcode = $postcode" : ""; $condition .= (!empty($reg)) ? " AND s.id_reg = $reg" : ""; $condition .= (!empty($county)) ? " AND s.id_county = $county" : ""; if(!empty($cat) && !is_numeric($cat)) { $select_id_cat = "SELECT id_cat FROM ". PREFIX ."cat WHERE par_cat = (SELECT MIN(id_cat) FROM ". PREFIX ."cat_lang WHERE par_cat = :par_cat AND name_cat_lang = :name_cat_lang)"; $req = $bdd->prepare($select_id_cat); $req->bindValue('par_cat', 0); $req->bindValue('name_cat_lang', htmlspecialchars_decode(stripslashes($cat))); $req->execute(); $result = $req->fetchAll(); $req->closeCursor(); $i = 0; if(!empty($result)) { $condition .= " AND ("; foreach($result as $id_cat) { if($i == 0) $condition .= "s.id_cat = ". (int) $id_cat[0] .""; else $condition .= " OR s.id_cat = ". (int) $id_cat[0] .""; $i++; } $condition .= ")"; } else $condition .= " AND s.id_cat = $cat_sql"; } elseif($cat != 0) $condition .= " AND s.id_cat = $cat_sql"; //Condition with keywords if(file_exists('includes/engine/search_a_optimized.php')) { require_once('includes/engine/search_a_optimized.php'); } else { if(!empty($array['title']) && is_array($array_keywords)) { $num = count($array_keywords); $condition .= " AND CONCAT_WS(' ', s.title) REGEXP '[[:<:]]$array_keywords[0][[:>:]]'"; if($num != 1) { for($i = 1; $i < $num; $i++) { if(empty($array_keywords[$i])) $condition .= ""; else $condition .= " AND CONCAT_WS(' ', s.title) REGEXP '[[:<:]]$array_keywords[$i][[:>:]]'"; } } } elseif(is_array($array_keywords)) { $num = count($array_keywords); $condition .= " AND CONCAT_WS(' ', s.title, s.text) REGEXP '[[:<:]]$array_keywords[0][[:>:]]'"; if($num != 1) { for($i = 1; $i < $num; $i++) { if(empty($array_keywords[$i])) $condition .= ""; else $condition .= " AND CONCAT_WS(' ', s.title, s.text) REGEXP '[[:<:]]$array_keywords[$i][[:>:]]' "; } } } } //Search with option price $price_min = isset($array['price_min']) ? $array['price_min'] : 0; $price_max = isset($array['price_max']) ? $array['price_max'] : 0; if($price_min != 0 && $price_max != 0) $condition .=" AND s.price BETWEEN $price_min AND $price_max"; elseif($price_min == 0 && $price_max != 0) $condition .=" AND s.price <= $price_max"; elseif($price_min != 0 && $price_max == 0) $condition .=" AND s.price >= $price_min"; //Condition for send alert if(isset($array['ad_date'])) $condition .= " AND s.date > ". $array['ad_date']; //Search by Geolocation // 111.045 - km (111.045 km per degree) // 69 - mile (69 statute miles per degree) $value_around = (!empty($param_gen['value_around']) && $param_gen['value_around'] == 'imperial') ? 69 : 111.045; $formule = $value_around."* DEGREES(ACOS(COS(RADIANS(s.lat)) * COS(RADIANS($visit_latitude)) * COS(RADIANS(s.lng) - RADIANS($visit_longitude)) + SIN(RADIANS(s.lat)) * SIN(RADIANS($visit_latitude))))"; if($distance > 0) $condition .= " AND $formule <= '$distance'"; //Search by dates if(!empty($array['search_start_date']) && !empty($array['search_end_date'])) { if(strptime($array['search_start_date'], '%d/%m/%Y') && strptime($array['search_end_date'], '%d/%m/%Y')) { $start = htmlspecialchars($array['search_start_date']); $start = strtotime(str_replace('/', '-', $start)); $end = htmlspecialchars($array['search_end_date']); $end = strtotime(str_replace('/', '-', $end)); $condition .=" AND (SELECT COUNT(start_date) FROM ". PREFIX ."ads_calendar WHERE id_ad = s.id_ad AND $start >= start_date AND $end <= end_date) > 0"; } } //SQL query $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.city, a.pictures_num, a.visit_num, i.name AS picture_name, v.video, $formule AS dist, s.lat, s.lng 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) 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 ". $condition; $sql_count = "SELECT (SELECT COUNT(*) FROM ". PREFIX ."ads_search s WHERE ". $condition ." AND s.status = 1 req_count) as count_par, (SELECT COUNT(*) FROM ". PREFIX ."ads_search s WHERE ". $condition ." AND s.status = 2 req_count) as count_pro, (SELECT COUNT(*) FROM ". PREFIX ."ads_search s WHERE ". $condition ." AND s.premium = 1 req_count) as count_premium"; $result = array('sql' => $sql, 'sql_count' => $sql_count); return $result; } /////////////////// //Get result of search options /////////////////// function make_options($array) { global $conn, $cache_search_digital, $cache_val_data, $cache_val_check; $bdd = $conn; $cat = (!empty($array['cat'])) ? (int) $array['cat'] : 0; $options = ''; //urgent ads and/or with photo and/or video $options .= (!empty($array['urgent'])) ? " AND s.urgent = 1" : ""; $options .= (!empty($array['picture'])) ? " AND s.picture_num = 1" : ""; //Search with options digital foreach($cache_search_digital as $v) { $id_cat = (int) $v['id_cat']; $id_cat_opt = (int) $v['id_cat_opt']; if($id_cat == $cat && isset($array['digital_'. $id_cat_opt .'_min'])) { $sel1 = (isset($array['digital_'. $id_cat_opt .'_min'])) ? (int) $array['digital_'. $id_cat_opt .'_min'] : 0; $sel2 = (isset($array['digital_'. $id_cat_opt .'_max'])) ? (int) $array['digital_'. $id_cat_opt .'_max'] : 0; if($id_cat_opt != 0) { if($sel1 != 0 && $sel2 != 0) $options .=" AND (SELECT val_digital FROM ". PREFIX ."ads_digital WHERE id_cat_opt = $id_cat_opt AND id_ad = s.id_ad) BETWEEN $sel1 AND $sel2"; elseif($sel1 == 0 && $sel2 != 0) $options .=" AND (SELECT val_digital FROM ". PREFIX ."ads_digital WHERE id_cat_opt = $id_cat_opt AND id_ad = s.id_ad) <= $sel2"; elseif($sel1 != 0 && $sel2 == 0) $options .=" AND (SELECT val_digital FROM ". PREFIX ."ads_digital WHERE id_cat_opt = $id_cat_opt AND id_ad = s.id_ad) >= $sel1"; } } } //Search with options data foreach($cache_val_data as $v) { $id_val_data = (int) $v['id_val_data']; $id_cat_opt = (int) $v['id_cat_opt']; if(!empty($array['data_'. $id_cat_opt]) && $array['data_'. $id_cat_opt] == $id_val_data) { $sel = $array['data_'. $id_cat_opt]; if(!empty($sel)) $options .= " AND (SELECT id_val_data FROM ". PREFIX ."ads_data WHERE id_cat_opt = $id_cat_opt AND id_val_data = $sel AND id_ad = s.id_ad) = $sel"; } } //Search with options checkbox foreach($cache_val_check as $v) { $id_val_check = (int) $v['id_val_check']; $id_cat_opt = (int) $v['id_cat_opt']; if(!empty($array['checkbox_'. $id_val_check])) { $sel = $array['checkbox_'. $id_val_check]; if(!empty($sel)) $options .= " AND (SELECT id_val_check FROM ". PREFIX ."ads_check WHERE id_cat_opt = $id_cat_opt AND id_val_check = $sel AND id_ad = s.id_ad) = $sel"; } } return $options; } /////////////////// //Remove expired ads //////////////////// function remove_expired_ads() { global $conn, $param_gen; $bdd = $conn; $now = time(); $limit = (int) ($param_gen['ads_val'] * 24 * 3600); $sql = "SELECT s.id_ad, a.title, a.email, a.lang FROM ". PREFIX ."ads_search s LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad WHERE ($now - s.date) >= $limit AND s.state = :state"; $req_delete = $bdd->prepare($sql); $req_delete->bindValue('state', 2); $req_delete->execute(); while($row = $req_delete->fetch()) { $id_ad = (int) $row['id_ad']; $title = stripslashes($row['title']); $email = stripslashes($row['email']); $lang = stripslashes($row['lang']); $sql = "UPDATE ". PREFIX ."ads_search SET state = :state WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('state', 4); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "UPDATE ". PREFIX ."shops SET ads_num = (SELECT COUNT(*) FROM ". PREFIX ."ads_search WHERE state = :state AND id_acc = (SELECT id_acc FROM ". PREFIX ."ads_search WHERE id_ad = :id_ad)) WHERE id_acc = (SELECT id_acc FROM ". PREFIX ."ads_search WHERE id_ad = :id_ad)"; $req = $bdd->prepare($sql); $req->bindValue('state', 2); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); send_expired_ad($email, $title, $lang); } $req_delete->closeCursor(); return true; } /////////////////// //Delete rejected ads //////////////////// function delete_rejected_ads() { global $conn, $param_gen; $bdd = $conn; $sql = "SELECT id_ad FROM ". PREFIX ."ads_search WHERE state = :state"; $req_delete = $bdd->prepare($sql); $req_delete->bindValue('state', 3); $req_delete->execute(); while($row = $req_delete->fetch()) { $id_ad = (int) $row['id_ad']; $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 ."purchases WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads_comments WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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 = "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 = "DELETE FROM ". PREFIX ."ads_options WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads_search WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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(); $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 = "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 = "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 = "DELETE FROM ". PREFIX ."ads_video WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); } $req_delete->closeCursor(); return true; } /////////////////// //Delete expired ads //////////////////// function delete_expired_ads() { global $conn, $param_gen; $bdd = $conn; $sql = "SELECT id_ad FROM ". PREFIX ."ads_search WHERE state = :state"; $req_delete = $bdd->prepare($sql); $req_delete->bindValue('state', 4); $req_delete->execute(); while($row = $req_delete->fetch()) { $id_ad = (int) $row['id_ad']; $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_comments WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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 = "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 = "DELETE FROM ". PREFIX ."ads_options WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads_search WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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(); $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 = "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 = "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 = "DELETE FROM ". PREFIX ."ads_video WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); } $req_delete->closeCursor(); return true; } /////////////////// //Delete unconfirmed ads //////////////////// function delete_unconfirmed_ads() { global $conn; $bdd = $conn; $now = time(); $limit = (int) (2 * 24 * 3600); $sql = "SELECT s.id_ad, a.title, a.email, a.lang FROM ". PREFIX ."ads_search s LEFT JOIN ". PREFIX ."ads a ON s.id_ad = a.id_ad WHERE ($now - s.date) >= $limit AND s.state = :state"; $req_delete = $bdd->prepare($sql); $req_delete->bindValue('state', 0); $req_delete->execute(); while($row = $req_delete->fetch()) { $id_ad = (int) $row['id_ad']; $title = stripslashes($row['title']); $email = stripslashes($row['email']); $lang = stripslashes($row['lang']); $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_comments WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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 = "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 = "DELETE FROM ". PREFIX ."ads_options WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads_search WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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(); $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 = "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 = "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 = "DELETE FROM ". PREFIX ."ads_video WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); send_unvalidate_ad($email, $title, $lang); } $req_delete->closeCursor(); return true; } /////////////////// //Delete deleteted ads //////////////////// function delete_deleted_ads() { global $conn, $param_gen; $bdd = $conn; $sql = "SELECT id_ad FROM ". PREFIX ."ads_search WHERE state = :state"; $req_delete = $bdd->prepare($sql); $req_delete->bindValue('state', 6); $req_delete->execute(); while($row = $req_delete->fetch()) { $id_ad = (int) $row['id_ad']; $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_comments WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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 = "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 = "DELETE FROM ". PREFIX ."ads_options WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads_search WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $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(); $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 = "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 = "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 = "DELETE FROM ". PREFIX ."ads_video WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); $sql = "DELETE FROM ". PREFIX ."ads WHERE id_ad = :id_ad"; $req = $bdd->prepare($sql); $req->bindValue('id_ad', $id_ad); $req->execute(); $req->closeCursor(); } $req_delete->closeCursor(); return true; } //////////////////////////////// //Send account search alert //////////////////////////////// function send_account_search_alert() { global $conn, $cache_regions; $bdd = $conn; $sql = "SELECT * FROM ".PREFIX."accounts_search WHERE send_mail = 1"; $req = $bdd->prepare($sql); $req->execute(); $searches = $req->fetchAll(); $req->closeCursor(); if($searches && sizeof($searches) > 0) { $to_send = array(); foreach($searches as $search) { parse_str($search['search'], $params); $distance = isset($params['distance']) ? $params['distance'] : 0; $visit_latitude = isset($search['visit_latitude']) ? $search['visit_latitude'] : 0; $visit_longitude = isset($search['visit_longitude']) ? $search['visit_longitude'] : 0; $keywords = isset($params['keywords']) && !empty($params['keywords']) ? trim(htmlspecialchars($params['keywords'], ENT_QUOTES)) : ''; $array_keywords = null; if(strlen($keywords) > 0) { $str = array('+', ''', '('); $str_replace = array(' ', ' ', ' '); $keywords_replace = str_replace($str, $str_replace, $keywords); $array_keywords = explode(' ', $keywords_replace); } unset($params['distance']); unset($params['keywords']); unset($params['visit_latitude']); unset($params['visit_longitude']); $params['search_start_date'] = strtotime(date('Y-m-d')); $params['search_end_date'] = $params['search_start_date'] + (24 * 3600); $params['ad_date'] = time() - (24 * 3600); $sql = make_sql($params, $array_keywords, $visit_latitude, $visit_longitude, $distance); $options = make_options($_GET); $nb_ads = get_nb_ads_search($sql['sql_count'], $options); if($nb_ads && ($nb_ads['count_par'] > 0 || $nb_ads['count_pro']) > 0) array_push($to_send, $search); } if(sizeof($to_send) > 0) { foreach($to_send as $send) { $account = get_info_member($send['id_acc']); if(isset($account) && isset($account['email'])) { parse_str($send['search'], $data); $title_search = strtolower(acc_search_to_string_title($data, $account['lang'])); send_account_search($send, $title_search, $account['email'], $account['lang']); } } } } return true; }