"""
    :copyright: (c) 2011 Local Projects, all rights reserved
    :license: Affero GNU GPL v3, see LICENSE for more details.
"""

from framework.log import log
  
def getLocationsWithScoring(db):
    data = []
    
    log.info("*** hit locations")

    try:
        # TODO
        # this is temporary until actual scoring is determined
        sql = """
select l.location_id
    ,l.name
    ,l.lat
    ,l.lon
    ,(select count(*) as count
            from project p
            inner join project__user opu on opu.project_id = p.project_id and opu.is_project_admin = 1
            where
            p.is_active = 1 
            and p.location_id = l.location_id) as num_projects
    ,(select count(*) as count
            from idea i
            where
            i.is_active = 1
            and i.location_id = l.location_id) as num_ideas
    ,(select count(*) as count
            from project_resource r
            where
            r.is_active = 1 and r.is_hidden = 0 and r.location_id = l.location_id) as num_project_resources
from location l where l.location_id > 0""";
        data = list(db.query(sql))
    except Exception, e:
        log.info("*** couldn't get locations")
        log.error(e)

    return data 
        
def getLocations(db):
    data = []

    try:
        sql = """select l.location_id, l.name, l.lat, l.lon from location l where l.location_id > 0
                order by l.location_id""";
        data = list(db.query(sql))
    except Exception, e:
        log.info("*** couldn't get locations")
        log.error(e)

    return data 

def getLocationInfo(db, locationId):
    info = {}
    
    try:
        sql = """select 'n_projects' as key_name, count(*) as num from project where location_id = $id
union
select 'n_ideas' as key_name, count(*) as num from idea where location_id = $id
union
select 'n_resources' as key_name, count(*) as num from project_resource where location_id = $id;"""
        data = list(db.query(sql, {'id':locationId}))

        for item in data:
            info[item.key_name] = item.num
    except Exception, e:
        log.info("*** couldn't get location info")
        log.error(e)

    return info

# deprecated ?
def getSimpleLocationDictionary(db):
    data = getLocations(db)
    
    locations = []
    
    for item in data:
        locations.append({'name':item.name, 'location_id':item.location_id})
        
    return locations
    
    
@@ -375,118 +375,267 @@
 n_id
+,
 %0A    
-,
 l.name
+,
 %0A    
-,
 l.lat
+,
 %0A    
-,
 l.lon
+,
 %0A    
-,(select count(*) as count%0A            from project p%0A            inner
+count(distinct p.project_id) as np,%0A    count(distinct i.idea_id) as ni,%0A    count(distinct r.project_resource_id) as npr%0Afrom location l %0A%09left join project p on p.location_id = l.location_id and p.is_active=1%0A    left
  joi
@@ -654,18 +654,15 @@
 ser 
-o
 pu on 
-opu
+p
 .pro
@@ -672,12 +672,11 @@
 t_id
- = p
+=pu
 .pro
@@ -687,17 +687,16 @@
 _id and 
-o
 pu.is_pr
@@ -714,73 +714,49 @@
  = 1
-%0A            where%0A            p.is_active = 1 %0A            and p
+ and p.is_active=1%0A%09left join idea i on l
 .loc
@@ -758,33 +758,33 @@
 l.location_id = 
-l
+i
 .location_id) as
@@ -783,144 +783,61 @@
 n_id
-)
  a
-s num_projects%0A    ,(select count(*) as count%0A            from idea i%0A            where%0A            i.is_active = 1%0A            and i
+nd i.is_active=1%0A%09left join project_resource r on l
 .loc
@@ -839,33 +839,33 @@
 l.location_id = 
-l
+r
 .location_id) as
@@ -864,119 +864,12 @@
 n_id
-)
  a
-s num_ideas%0A    ,(select count(*) as count%0A            from project_resource r%0A            where%0A           
+nd
  r.i
@@ -876,19 +876,17 @@
 s_active
- = 
+=
 1 and r.
@@ -898,18 +898,18 @@
 dden
- = 0 and r
+=0%0Awhere l
 .loc
@@ -909,33 +909,44 @@
 e l.location_id 
-=
+%3E 0%0Agroup by
  l.location_id) 
@@ -947,56 +947,29 @@
 n_id
-) as num_project_resources%0Afrom location l where
++l.lat+l.lon%0Aorder by
  l.l
@@ -982,17 +982,14 @@
 n_id
- %3E 0
 %22%22%22;%0A
+%0A
     
@@ -2313,16 +2313,17 @@
 ations%0A    %0A    
+%0A

Optimize location search query