public static function retrieveNbComments($topic_subject)
{
if (is_string($topic_subject)) {
return Doctrine_Query::create()->select('COUNT(p.id) nb_comments')->from('PunbbComm p, p.Topic t')->where('t.forum_id = 1 AND t.id = p.topic_id AND t.subject = ?', array($topic_subject))->execute()->getFirst()->nb_comments;
} else {
if (is_array($topic_subject)) {
$sql = 'SELECT COUNT(p.id) nb_comments, p2.subject FROM punbb_posts p LEFT JOIN punbb_topics p2 ON p.topic_id = p2.id ' . 'WHERE (p2.forum_id = 1 AND p2.id = p.topic_id AND p2.subject IN ( ' . "'" . implode($topic_subject, "', '") . "'" . ')) GROUP BY p2.subject';
return sfDoctrine::connection()->standaloneQuery($sql)->fetchAll();
} else {
return null;
}
}
}
public static function getOutOfDatePendingUserIds()
{
$max_pending_time = sfConfig::get('app_pending_users_lifetime');
$limit_date = time() - $max_pending_time * 24 * 60 * 60;
$sql = 'SELECT a.id FROM app_users_private_data a, app_users_groups ug ' . 'WHERE a.id = ug.user_id AND ug.group_id = 4 AND a.registered < ?';
$rs = sfDoctrine::connection()->standaloneQuery($sql, array($limit_date))->fetchAll();
$expired_users = array();
if (count($rs) > 0) {
foreach ($rs as $item) {
$expired_users[] = $item['id'];
}
}
return $expired_users;
}
/**
* loadData
*
* @param mixed $directory_or_file
* @param mixed $connectionName
* @access public
* @return void
*/
public function loadData($directory_or_file = null, $connectionName = null)
{
$this->connectionName = $connectionName;
$fixture_files = $this->getFiles($directory_or_file);
// wrap all database operations in a single transaction
$con = sfDoctrine::connection($connectionName);
try {
$con->beginTransaction();
$this->doLoadData($fixture_files);
$con->commit();
} catch (Exception $e) {
$con->rollback();
throw $e;
}
}
public static function MarkTopicAsread($topic_id, $last_post_time)
{
// let's reproduce behaviour of mark_topic_read() from punbb
// note that forum_id is always 1 for comments
$user_id = sfContext::getInstance()->getUser()->getId();
$sql = "SELECT last_visit, read_topics FROM punbb_users WHERE id='{$user_id}';";
$result = sfDoctrine::connection()->standaloneQuery($sql)->fetchAll();
$last_visit = $result[0]['last_visit'];
$read_topics = unserialize($result[0]['read_topics']);
if ($last_visit >= $last_post_time) {
return;
} else {
if (!empty($read_topics['f'][1]) && $read_topics['f'][1] >= $last_post_time) {
return;
} else {
if (!empty($read_topics['t'][$topic_id]) && $read_topics['t'][$topic_id] >= $last_post_time) {
return;
} else {
$read_topics['t'][$topic_id] = $last_post_time;
$sql = "UPDATE punbb_users SET read_topics='" . pg_escape_string(serialize($read_topics)) . "' WHERE id='{$user_id}';";
sfDoctrine::connection()->standaloneQuery($sql);
}
}
}
}
<?php
/**
* Batch that dumps list of ranges to be used in metaengine.
*
* @version $Id: dumpRangesForMetaengine.php 2231 2007-10-31 14:22:09Z alex $
*/
define('SF_ROOT_DIR', realpath(dirname(__FILE__) . '/..'));
define('SF_APP', 'frontend');
define('SF_ENVIRONMENT', 'prod');
define('SF_DEBUG', false);
require_once SF_ROOT_DIR . DIRECTORY_SEPARATOR . 'apps' . DIRECTORY_SEPARATOR . SF_APP . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.php';
// needed for doctrine connection to work
sfContext::getInstance();
$sql = 'SELECT n.name, ST_AsText(ST_Transform(a.geom, 4326)) as geom_wkt, a.id FROM areas a, areas_i18n n ' . 'WHERE a.id = n.id AND a.area_type = ? AND n.culture = ? ORDER BY n.name ASC';
$res = sfDoctrine::connection()->standaloneQuery($sql, array(1, 'fr'))->fetchAll();
$output = '';
foreach ($res as $r) {
$output .= sprintf("INSERT INTO regions (name, external_region_id, system_id, geom) VALUES ('%s', %d, %d, GeomFromEWKT('SRID=4326;%s'));\n\n", addslashes($r['name']), $r['id'], sfConfig::get('app_meta_engine_c2c_id'), $r['geom_wkt']);
}
$output .= "VACUUM ANALYSE;";
file_put_contents("metaengine_ranges.sql", $output);
public static function findLinkedDocsWithBestName($ids, $user_prefered_langs, $types = null, $get_associated_ids = false, $get_linked = true, $current_doc_ids = null)
{
if (!is_array($ids)) {
$ids = array($ids);
} elseif (!count($ids)) {
return array();
}
if (!$get_associated_ids) {
$where_array = $ids;
$where_ids = array();
foreach ($ids as $id) {
$where_ids[] = '?';
}
if ($get_linked) {
$select_id = 'linked_id';
$where_id = 'main_id';
} else {
$select_id = 'main_id';
$where_id = 'linked_id';
}
$where = "a.{$where_id} IN ( " . implode($where_ids, ', ') . " )";
if ($types) {
$where .= ' AND ( ';
if (!is_array($types)) {
$types = array($types);
}
$where2 = array();
foreach ($types as $type) {
$where2[] = 'a.type = ?';
$where_array[] = $type;
}
$where .= implode(' OR ', $where2) . ' )';
}
if (!empty($current_doc_ids)) {
if (!is_array($current_doc_ids)) {
$current_doc_ids = array($current_doc_ids);
}
$where_ids = array();
foreach ($current_doc_ids as $current_doc_id) {
$where_ids[] = '?';
$where_array[] = $current_doc_id;
}
$where .= " AND a.{$select_id} NOT IN ( " . implode(', ', $where_ids) . " )";
}
$doc_ids = "SELECT a.{$select_id} FROM app_documents_associations a WHERE {$where}";
} else {
$doc_associations = self::countAll($ids, $types, $current_doc_ids);
if (!count($doc_associations)) {
return array();
}
$doc_associations_norm = array();
$where_ids = $where_array = array();
foreach ($doc_associations as $association) {
$association_norm = array();
if (in_array($association['main_id'], $ids)) {
$association_norm['parent_id'] = $association['main_id'];
$association_norm['id'] = $association['linked_id'];
$association_norm['rel_parent'] = 'main_id';
} else {
$association_norm['parent_id'] = $association['linked_id'];
$association_norm['id'] = $association['main_id'];
$association_norm['rel_parent'] = 'linked_id';
}
$doc_associations_norm[] = $association_norm;
$where_ids[] = '?';
$where_array[] = $association_norm['id'];
}
$doc_ids = implode(', ', $where_ids);
}
$query = 'SELECT m.module, m.elevation, m.lon, m.lat, mi.id, mi.culture, mi.name, mi.search_name ' . 'FROM documents_i18n mi LEFT JOIN documents m ON mi.id = m.id ' . 'WHERE mi.id IN ' . "({$doc_ids}) " . 'ORDER BY mi.id ASC';
$results = sfDoctrine::connection()->standaloneQuery($query, $where_array)->fetchAll();
$out = self::setBestName($results, $user_prefered_langs);
if ($get_associated_ids) {
foreach ($out as $key => $result) {
foreach ($doc_associations_norm as $association_norm) {
if ($association_norm['id'] == $result['id']) {
$out[$key]['parent_relation'][$association_norm['parent_id']] = $association_norm['rel_parent'] === 'linked_id' ? 'child' : 'parent';
}
if ($association_norm['parent_id'] == $result['id']) {
$out[$key]['parent_relation'][$association_norm['id']] = $association_norm['rel_parent'] === 'linked_id' ? 'parent' : 'child';
}
}
}
}
return $out;
}
define('SF_ROOT_DIR', realpath(dirname(__FILE__) . '/..'));
define('SF_APP', 'frontend');
define('SF_ENVIRONMENT', 'prod');
define('SF_DEBUG', false);
define('GP_DIR', SF_ROOT_DIR . DIRECTORY_SEPARATOR . 'tmp/');
require_once SF_ROOT_DIR . DIRECTORY_SEPARATOR . 'apps' . DIRECTORY_SEPARATOR . SF_APP . DIRECTORY_SEPARATOR . 'config' . DIRECTORY_SEPARATOR . 'config.php';
// needed for doctrine connection to work
$context = sfContext::getInstance();
$zoom_levels = array('10' => 1, '20' => 2, '50' => 3, '100' => 4);
$default_zoom = 5;
$csv = '';
$nb_summits = 0;
foreach (Doctrine_Query::create()->select('a.id')->from('Area a')->where('a.area_type = 1')->limit(3)->execute() as $area) {
// summits
$sql = "SELECT s.id, n.name, s.lon, s.lat, s.elevation FROM summits s, summits_i18n n, areas a " . "WHERE a.id = " . $area->id . " AND s.id = n.id AND s.redirects_to IS NULL AND summit_type = 1 AND s.geom IS NOT NULL AND n.culture = 'fr' " . "AND intersects(a.geom, s.geom) AND a.geom && s.geom " . "ORDER BY s.elevation DESC, n.name ASC";
$summits = sfDoctrine::connection()->standaloneQuery($sql)->fetchAll();
$i = 0;
foreach ($summits as $summit) {
$nb_summits++;
$i++;
$zoom = $default_zoom;
foreach ($zoom_levels as $max => $level) {
if ($i <= $max) {
$zoom = $level;
break;
}
}
$csv .= sprintf('"%s";"%s";"%s";"http://www.camptocamp.org/summits/popup/%d/fr";"%d";"%d";"%d"' . "\n", $summit['name'], $summit['lon'], $summit['lat'], $summit['id'], $summit['elevation'], $zoom, $area->id);
}
}
file_put_contents(GP_DIR . 'sommets_c2c.csv', $csv);
function update_document()
{
global $argv, $argc, $conn, $comment, $is_map, $is_new_document, $culture, $name, $map_editor, $newgeom, $map_scale, $map_code, $region_type, $newgeomtext, $no_oldgeom, $oldgeom, $document_id, $prgmsg, $fullwipe, $keepassociations;
if ($argc < 5) {
usage();
}
if ($argv[2] != 'area' && $argv[2] != 'map') {
usage();
}
$is_map = $argv[2] === 'map';
if (!file_exists($argv[3])) {
info("Kml file does not exist\n\n");
usage();
}
if (!is_numeric($argv[4])) {
info("Invalid region or map id\n\n");
usage();
}
$document_id = intval($argv[4]);
$fullwipe = $argc === 6 && $argv[5] === 'fullwipe';
$keepassociations = $argc === 6 && $argv[5] === 'keepassociations';
$is_new_document = false;
info("Create geometry from kml file...\n");
$newgeomtext = text_geometry_from_file($argv[3]);
$newgeom = geometry_from_text($newgeomtext);
info("Validating geometry...\n");
// check that the new geometry is valid
if (!validate_geometry($newgeom)) {
die("The new geometry is invalid. Aborting...\n");
}
// we need to first delete old geometry and old geaoassociations
$oldgeom = $conn->standaloneQuery('SELECT geom FROM ' . ($is_map ? 'maps' : 'areas') . ' WHERE id=?', array($document_id))->fetchAll();
// check that document exists
if (!count($oldgeom)) {
die("Specified {$argv[2]} ({$document_id}) does not exist\n");
}
// Output warning if document has no geometry
$oldgeom = $oldgeom[0]['geom'];
if (is_null($oldgeom)) {
$no_oldgeom = true;
info("Warning: specified {$argv[2]} ({$document_id}) has no geometry...\n");
} else {
$no_oldgeom = false;
}
// first, remove geometry in a separate transaction if we are to update it
// no better way found...
if (!$no_oldgeom) {
info("Deleting old geometry...\n");
try {
$conn->beginTransaction();
$history_metadata = new HistoryMetadata();
$history_metadata->setComment('Delete geometry before update');
$history_metadata->set('is_minor', false);
$history_metadata->set('user_id', 2);
// C2C user
$history_metadata->save();
$area = Document::find($is_map ? 'Map' : 'Area', $document_id);
$area->set('geom_wkt', null);
$area->save();
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
throw $e;
}
info("Old geometry deleted\n");
}
// then delete geoassociations
// We only delete geoassociations where it is needed
// ie the parts of the old geometry that does not intersect with the new one
// If document has no previous geometry, we make sure to delete geoassociations
// rq: we only use buffer for areas, not for maps
if ($keepassociations) {
// do nothing
} else {
if ($no_oldgeom || $fullwipe) {
$geoassociations = GeoAssociation::findAllAssociations($document_id, null, 'both');
$prgmsg = "Delete all old geoassociations...";
info($prgmsg);
try {
$conn->beginTransaction();
$tot = count($geoassociations);
foreach ($geoassociations as $i => $geoassociation) {
progression($i, $tot);
$geoassociation->delete();
}
$conn->commit();
if (isset($deleted)) {
associations_result($deleted, false);
}
} catch (exception $e) {
$conn->rollback();
throw $e;
}
info("\n");
} else {
$deletegeom = $conn->standaloneQuery("SELECT ST_Difference('{$oldgeom}', '{$newgeom}')")->fetchAll();
$deletegeomb = $conn->standaloneQuery("SELECT ST_Difference(buffer('{$oldgeom}', 200), buffer('{$newgeom}', 200))")->fetchAll();
$deletegeom = $deletegeom[0]['st_difference'];
$deletegeomb = $deletegeomb[0]['st_difference'];
// for maps, we don't use buffer at all
if ($is_map) {
$deletegeomb = $deletegeom;
}
$queries = array();
// point geometry
$queries[] = array("SELECT id, module FROM documents WHERE geom && '{$deletegeomb}' " . "AND ST_Within(geom, '{$deletegeomb}') " . "AND module IN('summits', 'huts', 'sites', 'parkings', 'products', 'portals', 'images'" . ($is_map ? '' : ", 'users'") . ')', array());
$queries[] = array("SELECT id, module FROM documents WHERE geom && '{$deletegeomb}' " . "AND ST_Intersects(geom, '{$deletegeomb}') AND module" . ($is_map ? "='routes'" : " IN('routes', 'outings')"), array());
// for maps areas associations, we always compute 'full wipe', without buffer
$queries[] = array("SELECT id, module FROM documents WHERE geom && '{$oldgeom}' " . "AND ST_Intersects(geom, '{$oldgeom}') AND module='" . ($is_map ? 'areas' : 'maps') . "'", array($document_id, $document_id));
$results_a = array();
foreach ($queries as $query) {
$results_a[] = sfDoctrine::connection()->standaloneQuery($query[0], $query[1])->fetchAll();
}
$results = array();
foreach ($results_a as $results_set) {
foreach ($results_set as $d) {
$results[] = $d;
}
}
$tot = count($results);
$prgmsg = "Delete obsolete geoassociations...";
info($prgmsg);
try {
$conn->beginTransaction();
foreach ($results as $i => $d) {
progression($i, $tot);
$geoassociation = GeoAssociation::find($document_id, $d['id'], null, false);
if ($geoassociation !== false) {
$geoassociation->delete();
$deleted[$d['module']] = isset($deleted[$d['module']]) ? $deleted[$d['module']] + 1 : 1;
}
// for routes and outings, we need to check that they are not intersecting the new geom
// because they shouldn't be unlinked in that case
// (it's quite unconvenient, but no best way found)
if (in_array($d['module'], array('outings', 'routes'))) {
$query = "SELECT ST_Intersects(geom, ST_Buffer('{$newgeom}', 200)) FROM " . $d['module'] . " WHERE id=?";
$result = sfDoctrine::connection()->standaloneQuery($query, array($d['id']))->fetchAll();
$result = $result[0]['st_intersects'];
if ($result) {
continue;
}
}
// inherited docs: we delete geoassociations for the 'inherited docs' from sites, routes and summits
// but not if they already have a geometry (gps track)
switch ($d['module']) {
case 'sites':
case 'routes':
if ($is_map) {
break;
}
// maps are not linked to outings
$associated_outings = Association::findAllAssociatedDocs($d['id'], array('id', 'geom_wkt'), $d['module'] === 'routes' ? 'ro' : 'to');
if (count($associated_outings)) {
foreach ($associated_outings as $outing) {
if (!$outing['geom_wkt']) {
$geoassociation = GeoAssociation::find($document_id, $outing['id'], null, false);
if ($geoassociation !== false) {
$geoassociation->delete();
$deleted['outings'] = isset($deleted['outings']) ? $deleted['outings'] + 1 : 1;
}
}
}
}
break;
case 'summits':
// if summit is of type raid, we should not try to update its routes and outings summit_type=5
$summit = Document::find('Summit', $d['id']);
if ($summit->get('summit_type') == 5) {
break;
}
$associated_routes = Association::findAllAssociatedDocs($d['id'], array('id', 'geom_wkt'), 'sr');
if (count($associated_routes)) {
foreach ($associated_routes as $route) {
$i = $route['id'];
if (!$route['geom_wkt']) {
$geoassociation = GeoAssociation::find($i, $document_id, null, false);
if ($geoassociation !== false) {
$geoassociation->delete();
$deleted['routes'] = isset($deleted['routes']) ? $deleted['routes'] + 1 : 1;
}
if (!$is_map) {
$associated_outings = Association::findAllAssociatedDocs($i, array('id', 'geom_wkt'), 'ro');
if (count($associated_outings)) {
foreach ($associated_outings as $outing) {
$j = $outing['id'];
if (!$outing['geom_wkt']) {
$geoassociation = GeoAssociation::find($j, $document_id, null, false);
if ($geoassociation !== false) {
$geoassociation->delete();
$deleted['outings'] = isset($deleted['outings']) ? $deleted['outings'] + 1 : 1;
}
}
}
}
}
}
}
}
break;
}
}
info("\n");
$conn->commit();
if (isset($deleted)) {
associations_result($deleted, false);
}
} catch (exception $e) {
$conn->rollback();
throw $e;
}
}
}
// import new geometry into database and create geoassociations
import_new_geometry();
}
public static function listRecentInTime2($module, $mean_time)
{
$sql = 'SELECT d.document_id, d.culture FROM app_documents_versions d ' . 'LEFT JOIN app_documents_archives a ON d.document_id = a.id WHERE a.id = 106987 GROUP BY d.document_id, d.culture';
return sfDoctrine::connection()->standaloneQuery($sql)->fetchAll();
}
public static function findWithBestName($ids, $user_prefered_langs, $types = null, $current_doc_ids = null)
{
if (!is_array($ids)) {
$ids = array($ids);
}
$where_array = array();
$where = "a.main_id IN ( '" . implode($ids, "', '") . "' )";
if ($types) {
$where .= ' AND ( ';
if (!is_array($types)) {
$types = array($types);
}
$where2 = array();
foreach ($types as $type) {
$where2[] = 'a.type = ?';
$where_array[] = $type;
}
$where .= implode(' OR ', $where2) . ' )';
}
if (!empty($current_doc_ids)) {
if (!is_array($current_doc_ids)) {
$current_doc_ids = array($current_doc_ids);
}
$where .= " AND a.linked_id NOT IN ( '" . implode($current_doc_ids, "', '") . "' )";
}
$doc_ids = "SELECT a.linked_id FROM app_geo_associations a WHERE {$where}";
$query = 'SELECT m.module, mi.id, mi.culture, mi.name, mi.search_name ' . 'FROM documents_i18n mi LEFT JOIN documents m ON mi.id = m.id ' . 'WHERE mi.id IN ' . "({$doc_ids}) " . 'ORDER BY mi.id ASC';
$results = sfDoctrine::connection()->standaloneQuery($query, $where_array)->fetchAll();
$out = self::setBestName($results, $user_prefered_langs);
return $out;
}
/**
* Retrieves an array of array(document_id, culture) of recently CREATED outings in a given mean time (in seconds).
*/
public static function listRecentInTime($mean_time)
{
$sql = 'SELECT d.document_id, d.culture, d.documents_versions_id, a.search_name FROM app_documents_versions d ' . 'LEFT JOIN outings_i18n a ON (d.document_id = a.id AND d.culture = a.culture) ' . "WHERE d.version = 1 AND (AGE(NOW(), d.created_at) < ( {$mean_time} * interval '1 second')) " . 'ORDER BY d.documents_versions_id DESC';
$outings = array();
foreach (sfDoctrine::connection()->standaloneQuery($sql)->fetchAll() as $outing) {
$id = $outing['document_id'];
$outings[$id] = $outing;
// if outing is available in several cultures, oldest one is the one
}
if (!empty($outings)) {
// remove outings having culture version already transmitted (older than $mean_time)
$ids = implode(',', array_keys($outings));
$sql = "select distinct document_id from app_documents_versions where document_id in ({$ids}) and AGE(NOW(), created_at) > ( {$mean_time} * interval '1 second' )";
foreach (sfDoctrine::connection()->standaloneQuery($sql)->fetchAll() as $result) {
$id = $result['document_id'];
unset($outings[$id]);
}
}
return $outings;
}
public static function getLastDocs($summit_separator = ': ')
{
/*
$q = Doctrine_Query::create()
->select('i.name, i.search_name, i.culture, a.id, a.module')
->from('DocumentVersion d')
->leftJoin('d.DocumentArchive a')
->leftJoin('d.DocumentI18nArchive i')
->where("d.version = 1 AND a.module != 'outings' AND a.module != 'users' AND a.module != 'images'")
->limit(20)
->orderBy('d.created_at DESC');
//return $q->execute(array(), Doctrine::FETCH_ARRAY); // FIXME: returns nothing!?
$sql = $q->getSql();
*/
// following query is ok, but the displayed name is the first name given to the document, should be the last one
/*
$sql = 'SELECT a2.id AS id, a2.module AS module, a3.name AS name, a3.search_name AS search_name, a3.culture AS culture ' .
'FROM app_documents_versions a ' .
'LEFT JOIN app_documents_archives a2 ON a.document_archive_id = a2.document_archive_id ' .
'LEFT JOIN app_documents_i18n_archives a3 ON a.document_i18n_archive_id = a3.document_i18n_archive_id ' .
"WHERE (a.version = 1 AND a2.module != 'outings' AND a2.module != 'users' AND a2.module != 'images' AND a2.module != 'articles') " .
'ORDER BY a.created_at DESC LIMIT 20';*/
// this one uses last document name
$sql = 'SELECT sub.id AS id, sub.module AS module, a3.name AS name, a3.search_name AS search_name, a3.culture AS culture ' . 'FROM ' . '(SELECT a2.id AS id, a2.module AS module, a.culture AS culture FROM app_documents_versions a ' . 'LEFT JOIN app_documents_archives a2 ON a.document_archive_id = a2.document_archive_id ' . "WHERE (a.version = 1 AND a2.module NOT IN ('outings', 'users', 'images', 'articles') AND a2.redirects_to IS NULL) " . 'ORDER BY a.created_at DESC LIMIT 20) AS sub ' . 'LEFT JOIN documents_i18n a3 ON a3.id = sub.id AND sub.culture = a3.culture';
$docs = sfDoctrine::connection()->standaloneQuery($sql)->fetchAll();
// get summit name for routes items
$routes = Route::addBestSummitName(array_filter($docs, array('c2cTools', 'is_route')), $summit_separator);
foreach ($routes as $key => $route) {
$docs[$key] = $route;
}
return $docs;
}
public static function getBox2d($id, $module = null)
{
$values[] = $id;
$table = !empty($module) ? $module : 'documents';
$sql = "SELECT Box2D(geom) FROM {$table} AS d WHERE d.id = ?";
$rs = sfDoctrine::connection()->standaloneQuery($sql, $values)->fetchObject();
$out = $rs->box2d;
$begin = strpos($out, '(') + 1;
$end = strrpos($out, ')');
return substr($out, $begin, $end - $begin);
}
public static function getSubSummits($id)
{
$query = 'SELECT m.id FROM summits m WHERE m.id IN ' . '(SELECT a.linked_id FROM app_documents_associations a WHERE a.main_id = ? AND type = ?) ' . 'ORDER BY m.id ASC';
$results = sfDoctrine::connection()->standaloneQuery($query, array($id, 'ss'))->fetchAll();
return $results;
}
