Added comment for region processing.

[philipp/winterrodeln/wrfeed.git] / wrfeed / controllers / berichte.py

diff --git a/wrfeed/controllers/berichte.py b/wrfeed/controllers/berichte.py
index f8afb04794472f560fd630a4c2a7c6b93e4a3708..878a83cf87df41d5a3faa642e98414d05658f5e5 100644 (file)
--- a/wrfeed/controllers/berichte.py
+++ b/wrfeed/controllers/berichte.py
@@ -1,11 +1,12 @@
-#!/usr/bin/python2.6
+#!/usr/bin/python2.7

 # -*- coding: UTF-8 -*-
 """Creates an Atom-Feed for single or multiple winterrodeln sled reports.
 
 Format:
 # -*- coding: UTF-8 -*-
 """Creates an Atom-Feed for single or multiple winterrodeln sled reports.
 
 Format:

-    http://www.winterrodeln.org/feeds/berichte/alle
-    http://www.winterrodeln.org/feeds/berichte/bahn/kemater_alm
-    http://www.winterrodeln.org/feeds/berichte/bahnen/22+42+132 
+    http://www.winterrodeln.org/feed/berichte/alle
+    http://www.winterrodeln.org/feed/berichte/bahn/kemater_alm
+    http://www.winterrodeln.org/feed/berichte/bahnen/5+280+251
+    http://www.winterrodeln.org/feed/berichte/region/osttirol

 See:
     http://www.atompub.org/
     http://effbot.org/zone/element.htm
 See:
     http://www.atompub.org/
     http://effbot.org/zone/element.htm


@@ -16,9 +17,11 @@ import datetime
 from xml.etree.ElementTree import Element, SubElement, tostring
 
 from sqlalchemy.engine import create_engine
 from xml.etree.ElementTree import Element, SubElement, tostring
 
 from sqlalchemy.engine import create_engine

+from osgeo import ogr

 
 import logging
 
 
 import logging
 

+import webob.exc

 from pylons import request, response, session, config, tmpl_context as c, url
 from pylons.controllers.util import abort, redirect
 
 from pylons import request, response, session, config, tmpl_context as c, url
 from pylons.controllers.util import abort, redirect
 


@@ -27,59 +30,92 @@ from wrfeed.lib.base import BaseController, render
 log = logging.getLogger(__name__)
 
 
 log = logging.getLogger(__name__)
 
 

-def create_feed(page_title=None, page_ids=None):
-    """If a page_title is given, only the reports for the given sled are shown.
+def create_feed(page_title=None, page_ids=None, region_name=None):
+    """If a page_title is given, only the reports for the given sledrun are shown.

     If a list of page_ids is given, only the reports for the selected pages are shown.
     If a list of page_ids is given, only the reports for the selected pages are shown.

+    If a region name (lower case) is given, the reports just for this region are shown.

     Otherwise, all reports are shown."""
 
     engine = create_engine(config['sqlalchemy.url'])
     Otherwise, all reports are shown."""
 
     engine = create_engine(config['sqlalchemy.url'])

+    limit = int(config['feedentrylimit'])

     conn = engine.connect()
 
     conn = engine.connect()
 

-    select = "select id, page_title, date_report, date_entry, `condition`, description, author_name, author_username from wrreport "
+    select = 'select wrreport.page_id, wrreport.page_title, wrreport.id, date_report, date_entry, `condition`, description, author_name, author_userid, author_username, position_longitude, position_latitude from wrreport left outer join wrsledruncache on wrreport.page_id=wrsledruncache.page_id'
+    where = 'where date_invalid > now() and delete_date is null'
+    order = 'order by id desc'
+    limit = 'limit {0}'.format(limit)
+    params = []
+

     if not page_title is None:
         # page_title is given
         page_title = page_title.replace('_', ' ')
     if not page_title is None:
         # page_title is given
         page_title = page_title.replace('_', ' ')

-        sql = select + "where lcase(page_title) = lcase(%s) and date_invalid > now() and delete_date is null order by id desc limit 50"
-        result = conn.execute(sql, page_title)
+        where += ' and lcase(wrreport.page_title) = lcase(%s)'
+        params += [page_title]

     elif not page_ids is None:
         # a list of page_ids is given
     elif not page_ids is None:
         # a list of page_ids is given

-        sql = [select + "where "]
-        if len(page_ids) > 0:
-            sql += '('
-            sql += " or ".join(['page_id=%s' for page_id in page_ids])
-            sql += ') '
-        sql += 'and date_invalid > now() and delete_date is null order by id desc limit 50'
-        page_ids_str = [str(page_id) for page_id in page_ids]
-        result = conn.execute("".join(sql), *page_ids_str)
+        if len(page_ids) == 0:
+            raise webob.exc.HTTPBadRequest()
+        where += ' and ('
+        where += " or ".join(['wrreport.page_id=%s' for page_id in page_ids])
+        where += ')'
+        params += map(str, page_ids)
+    elif not region_name is None:
+        # a name of a region is given
+        # (1) find out whether the region exists
+        subselect = 'select aswkb(border) as border_wkb from wrregion where name=lcase(%s)'
+        subresult = conn.execute(subselect, region_name)
+        if subresult.rowcount == 0:
+            # no region with such a name
+            raise webob.exc.HTTPNotFound()
+        assert subresult.rowcount == 1
+        row = subresult.fetchone()
+        # (2) now we have the border
+        border_wkb = row['border_wkb'] # border as WKB
+        where += ' and contains(geomfromwkb(%s), point(position_longitude, position_latitude))'
+        params += [border_wkb]
+        # the following variables are needed for the additional filtering below
+        border = ogr.CreateGeometryFromWkb(border_wkb)
+        point = ogr.Geometry(ogr.wkbPoint)

     else:
         # user wants to have all reports
     else:
         # user wants to have all reports

-        sql = select + "where date_invalid > now() and delete_date is null order by id desc limit 50"
-        result = conn.execute(sql)
-
+        pass
+    sql = ' '.join([select, where, order, limit])
+    result = conn.execute(sql, *params)

 
 

-    feed = Element("feed", xmlns="http://www.w3.org/2005/Atom")
+    feed = Element("feed", xmlns="http://www.w3.org/2005/Atom", attrib={'xmlns:georss': 'http://www.georss.org/georss', 'xmlns:wr': 'http://www.winterrodeln.org/schema/wrreport'})

     feed_title = SubElement(feed, "title")
     feed_title.text = "Winterrodeln Rodelbahnberichte"
     feed_id = SubElement(feed, "id")
     if not page_title is None:
     feed_title = SubElement(feed, "title")
     feed_title.text = "Winterrodeln Rodelbahnberichte"
     feed_id = SubElement(feed, "id")
     if not page_title is None:

-        feed_id.text = url(controller='berichte', action='bahn', id=page_title)
+        feed_id.text = url(qualified=True, controller='berichte', action='bahn', id=page_title)

     elif not page_ids is None:
     elif not page_ids is None:

-        feed_id.text = url(controller='berichte', action='bahnen', id="+".join(page_ids_str))
+        feed_id.text = url(qualified=True, controller='berichte', action='bahnen', id="+".join(map(str, page_ids)))
+    elif not region_name is None:
+        feed_id.text = url(qualified=True, controller='berichte', action='region', id=region_name)

     else:
     else:

-        feed_id.text = url(controller='berichte', action='alle')
+        feed_id.text = url(qualified=True, controller='berichte', action='alle')

     feed_updated = SubElement(feed, "updated")
     feed.append(Element("link", rel="self", href=feed_id.text))
 
     last_updated = None
     for row in result:
     feed_updated = SubElement(feed, "updated")
     feed.append(Element("link", rel="self", href=feed_id.text))
 
     last_updated = None
     for row in result:

-        id, page_title, date_report, date_entry, condition, description, author_name, author_username = row
+        page_id, page_title, report_id, date_report, date_entry, condition, description, author_name, author_userid, author_username, lon, lat = row
+
+        if not region_name is None:
+            # mysql 5.5 doesn't have specification conform geospacial functions.
+            # http://dev.mysql.com/doc/refman/5.5/en/functions-for-testing-spatial-relations-between-geometric-objects.html
+            # therefore we have to do further filtering here.
+            point.SetPoint(0, lon, lat)
+            if not point.Within(border):
+                continue
+

         page_title_url = page_title.replace(u' ', u'_')
         entry = SubElement(feed, "entry")
         entry_title = SubElement(entry, "title")
         entry_title.text = page_title
         entry.append(Element("link", rel="alternate", href=u"http://www.winterrodeln.org/wiki/{0}".format(page_title_url), type="text/html", hreflang="de"))
         entry_id = SubElement(entry, "id")
         page_title_url = page_title.replace(u' ', u'_')
         entry = SubElement(feed, "entry")
         entry_title = SubElement(entry, "title")
         entry_title.text = page_title
         entry.append(Element("link", rel="alternate", href=u"http://www.winterrodeln.org/wiki/{0}".format(page_title_url), type="text/html", hreflang="de"))
         entry_id = SubElement(entry, "id")

-        entry_id.text = u"http://www.winterrodeln.org/feeds/schneelage/{0}/{1}".format(page_title_url, id)
+        entry_id.text = u"http://www.winterrodeln.org/wiki/{0}#{1}".format(page_title_url, report_id)

         entry_updated = SubElement(entry, "updated")
         entry_updated.text = date_entry.isoformat() + "+01:00"
         if last_updated is None: last_updated = date_entry
         entry_updated = SubElement(entry, "updated")
         entry_updated.text = date_entry.isoformat() + "+01:00"
         if last_updated is None: last_updated = date_entry


@@ -100,8 +136,21 @@ def create_feed(page_title=None, page_ids=None):
         entry_content_description.text = description
         entry_author = SubElement(entry, "author")
         entry_author_name = SubElement(entry_author, "name")
         entry_content_description.text = description
         entry_author = SubElement(entry, "author")
         entry_author_name = SubElement(entry_author, "name")

-        if author_name is None: entry_author_name.text = "Anonymous user"
+        if author_name is None or len(author_name.strip()) == 0: entry_author_name.text = "Anonymous user"

         else: entry_author_name.text = author_name
         else: entry_author_name.text = author_name

+        if not lon is None and not lat is None:
+            entry_geo = SubElement(entry, "georss:point")
+            entry_geo.text = "{lat} {lon}".format(lat=lat, lon=lon)
+        entry_wrreport = SubElement(entry, "wr:report")
+        entry_wrreport.attrib['report_id'] = str(report_id)
+        entry_wrreport.attrib['page_id'] = str(page_id)
+        entry_wrreport.attrib['page_title'] = page_title
+        entry_wrreport.attrib['date_report'] = str(date_report)
+        entry_wrreport.attrib['date_entry'] = str(date_entry)
+        entry_wrreport.attrib['condition'] = "0" if condition is None else str(condition)
+        entry_wrreport.attrib['author_name'] = author_name
+        entry_wrreport.attrib['author_username'] = "" if author_userid is None else author_username
+        entry_wrreport.text = description

 
     if last_updated is None: last_updated = datetime.datetime.now()
     feed_updated.text = last_updated.isoformat() + "+01:00"
 
     if last_updated is None: last_updated = datetime.datetime.now()
     feed_updated.text = last_updated.isoformat() + "+01:00"


@@ -115,20 +164,40 @@ def create_feed(page_title=None, page_ids=None):
 class BerichteController(BaseController):
 
     def alle(self):
 class BerichteController(BaseController):
 
     def alle(self):

-        """http://www.winterrodeln.org/feeds/berichte/alle"""
+        """Handles URLs like
+        http://127.0.0.1:5000/berichte/alle
+        http://www.winterrodeln.org/feed/berichte/alle
+        """

         response.content_type = 'application/atom+xml'
         return create_feed()
 
 
     def bahn(self, id):
         response.content_type = 'application/atom+xml'
         return create_feed()
 
 
     def bahn(self, id):

-        """http://www.winterrodeln.org/feeds/berichte/bahn/kemater_alm"""
+        """Handles URLs like
+        http://127.0.0.1:5000/berichte/bahn/kemater_alm
+        http://www.winterrodeln.org/feed/berichte/bahn/kemater_alm
+        """

         response.content_type = 'application/atom+xml'
         return create_feed(page_title=id)
 
 
     def bahnen(self, id):
         response.content_type = 'application/atom+xml'
         return create_feed(page_title=id)
 
 
     def bahnen(self, id):

-        """http://www.winterrodeln.org/feeds/berichte/bahnen/22+42+132"""
+        """Handles URLs like
+        http://127.0.0.1:5000/berichte/bahnen/5+280+251
+        http://www.winterrodeln.org/feed/berichte/bahnen/5+280+251
+        """

         page_ids = id.split('+')
         page_ids = id.split('+')

-        page_ids = [int(page_id) for page_id in page_ids]
+        try:
+            page_ids = [int(page_id) for page_id in page_ids]
+        except ValueError:
+            abort(400) # bad request

         response.content_type = 'application/atom+xml'
         return create_feed(page_ids=page_ids)
         response.content_type = 'application/atom+xml'
         return create_feed(page_ids=page_ids)

+
+    def region(self, id):
+        """Handles URLs like
+        http://www.winterrodeln.org/feed/berichte/region/osttirol
+        """
+        response.content_type = 'application/atom+xml'
+        return create_feed(region_name=id)
+