def test_parse_city(self):

        response = '''
            {"response":[
                {"cid":1,"title":"Москва","region":"Regione Abruzzo область"},
                {"cid":1074996,"title":"Москва","area":"Порховский район","region":"Псковская область"},
                {"cid":1102561,"title":"Москва","area":"Пеновский район","region":"Тверская область"},
                {"cid":1130701,"title":"Москва","area":"Верхошижемский район","region":"Кировская область"}
            ]}
            '''
        country = CountryFactory.create(remote_id=1)
        instance = City(country=country)
        instance.parse(json.loads(response)['response'][0])
        instance.save()

        self.assertEqual(instance.remote_id, 1)
        self.assertEqual(instance.name, u'Москва')

        instance = City(country=country)
        instance.parse(json.loads(response)['response'][1])
        instance.save()

        self.assertEqual(instance.remote_id, 1074996)
        self.assertEqual(instance.name, u'Москва')
        self.assertEqual(instance.area, u"Порховский район")
        self.assertEqual(instance.region, u"Псковская область")

 def test01_setup(self):
     "Setting up for related model tests."
     for name, state, lon, lat in cities:
         loc = Location(point=Point(lon, lat))
         loc.save()
         c = City(name=name, state=state, location=loc)
         c.save()

def create_tables():
    from models import User, Message, Note, Relationship, City, Pinche
    
    # User.create_table()
    Relationship.create_table()
    Note.create_table()
    Message.create_table()
    City.create_table()
    Pinche.create_table()

def postCity():
    try:
        city = City()
        if "name" in request.form: city.name = request.form["name"]
        if "maxLat" in request.form: city.pack = request.form["maxLat"]
        if "maxLon" in request.form: city.pack = request.form["maxLon"]
        if "minLat" in request.form: city.pack = request.form["minLat"]
        if "minLon" in request.form: city.pack = request.form["minLon"]

        db.session.add(city)
        db.session.commit()
        return make_response(jsonify( { 'ok': 'city created' } ), 200)
    except:
        return make_response(jsonify( { 'error': 'Server Error' } ), 500)

    def test11_lookup_insert_transform(self):
        "Testing automatic transform for lookups and inserts."
        if DISABLE:
            return
        # San Antonio in 'WGS84' (SRID 4326)
        sa_4326 = "POINT (-98.493183 29.424170)"
        wgs_pnt = fromstr(sa_4326, srid=4326)  # Our reference point in WGS84

        # Oracle doesn't have SRID 3084, using 41157.
        if SpatialBackend.oracle:
            # San Antonio in 'Texas 4205, Southern Zone (1983, meters)' (SRID 41157)
            # Used the following Oracle SQL to get this value:
            #  SELECT SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(SDO_GEOMETRY('POINT (-98.493183 29.424170)', 4326), 41157)) FROM DUAL;
            nad_wkt = "POINT (300662.034646583 5416427.45974934)"
            nad_srid = 41157
        else:
            # San Antonio in 'NAD83(HARN) / Texas Centric Lambert Conformal' (SRID 3084)
            nad_wkt = (
                "POINT (1645978.362408288754523 6276356.025927528738976)"
            )  # Used ogr.py in gdal 1.4.1 for this transform
            nad_srid = 3084

        # Constructing & querying with a point from a different SRID. Oracle
        # `SDO_OVERLAPBDYINTERSECT` operates differently from
        # `ST_Intersects`, so contains is used instead.
        nad_pnt = fromstr(nad_wkt, srid=nad_srid)
        if SpatialBackend.oracle:
            tx = Country.objects.get(mpoly__contains=nad_pnt)
        else:
            tx = Country.objects.get(mpoly__intersects=nad_pnt)
        self.assertEqual("Texas", tx.name)

        # Creating San Antonio.  Remember the Alamo.
        sa = City(name="San Antonio", point=nad_pnt)
        sa.save()

        # Now verifying that San Antonio was transformed correctly
        sa = City.objects.get(name="San Antonio")
        self.assertAlmostEqual(wgs_pnt.x, sa.point.x, 6)
        self.assertAlmostEqual(wgs_pnt.y, sa.point.y, 6)

        # If the GeometryField SRID is -1, then we shouldn't perform any
        # transformation if the SRID of the input geometry is different.
        # SpatiaLite does not support missing SRID values.
        if not SpatialBackend.spatialite:
            m1 = MinusOneSRID(geom=Point(17, 23, srid=4326))
            m1.save()
            self.assertEqual(-1, m1.geom.srid)

    def load2mysql(self):
        with open(self.path) as f:
            provinces = json.load(f)

        for province in provinces:
            # print province["name"]
            p = Province(name=province["name"])
            p.save()
            for city in province["city"]:
                # print " %s" % city["name"]
                c = City(name=city["name"], province=p)
                c.save()
                for area in city["area"]:
                    # print "  %s" % area
                    cou = County(name=area, city=c)
                    cou.save()

def view_city(environ, id_region=None):
    if not id_region:
        return ''
    city_list = City.filter(region_id=int(id_region[0]))
    response = u'<option value="">---</option>'
    for city in city_list:
        response += u'<option value="{0}">{1}</option>'.format(city.id, city.name)
    return HttpResponse(response)

def homepage():
    query = request.args.get("postal_code", "84510")

    results = []

    for city in City.select().where(City.postal_code == query):
        results.append({"insee": city.insee, "name": city.name, "postal_code": city.postal_code, "label": city.label})

    return jsonify(results=results)

    def seed_cities(self, number):
        """
        Crée des enregistrements aléatoires dans la table des villes
        :param number: nombre de villes
        :return: void
        """
        for i in range(0, number):
            city = City()
            city.name = self.fake.city()
            city.country = self.fake.country()
            city.is_capital = self.fake.pybool()
            self.city_service.save(city)

        capitals = self.city_service.all_capitals()
        non_capitals = self.city_service.all_non_capitals()

        for city in non_capitals:
            city.capital_id = random.choice(capitals).id
            self.city_service.save(city)

def create_or_getCity(cityName, state, lat,lon):
    try:
        c = City.objects.get(name=cityName)
    except City.DoesNotExist:        
            r = requests.get("http://photon.komoot.de/api/?q={},{}&limit=1&lat={}&lon={}".format(cityName, state.name, lat,lon)).json()
            
            lastCity = City.objects.latest('created_on')
            c = City()
            c.id = lastCity.id +1
            c.name=cityName        
            c.stateID=state.id
            c.lat= r['features'][0]['geometry']['coordinates'][1]
            c.lon= r['features'][0]['geometry']['coordinates'][0]
            c.live="true"
            c.save()
    return c

def main():
    cities = City.select()
    city_dict = {}
    for city in cities:
        if city.parent is None:
            city_dict[city.id] = []
            city_dict[city.id].append({'id': city.id, 'name': city.name})
        else:
            city_dict[city.parent.id].append({'id': city.id, 'name': city.name})

    print(json.dumps(city_dict, ensure_ascii=False))

    def save_to_db(self, dic):
        assert all(map(dic.has_key, ['title', 'original_price', 'price', 'detail', 'url'])),\
            "Information incomplete."
        
        url = dic['url']
        original_price = dic['original_price'].text.encode('utf8')
        price = dic['price'].text.encode('utf8')
        title = dic['title'].text # title is unicode
        detail = dic['detail'].renderContents(encoding='utf8')
        detail = utils.clean_detail(detail, self.home_url)
            
        # Data formatting & validation.
        try:
            original_price, price = map(lambda s: int(re.search(r'(\d+)', s).group()),
                                        [original_price, price])
        except TypeError:
            logging.error("Price conversion failed. Detailed info: %s", [original_price, price])
            return
        except AttributeError:
            logging.error("Regex failed on %s", [original_price, price])
            return
        
        if len(title) > 500 or len(title) < 10:
            logging.error("Title length too short or too long : %s", title)
            return
        
        if len(detail) < 20:
            logging.error("Detail too short. %s", detail)
            return

        # Save to db.
        try:
            site = Site.select(Site.q.url == self.home_url)
            assert(site.count() == 1), "%s not found or dups." % self.home_url
            
            title = utils.lstrip(title, [s.decode('utf8') for s in ('今日团购', '今日精选', '：')])
            title = title.strip()
            title='[%s] %s' % (site[0].name, title)
            
            city_name = self.index_urls[url]
            city = City.select(City.q.name == city_name.decode('utf8'))
            assert city.count() == 1, "%s not found or dups." % city_name
            cityID = city[0].id
            
            if Deal.select(AND(Deal.q.title == title, Deal.q.cityID == cityID)).count() > 0:
                logging.info("Title dups %s" % title)
                return
            deal = Deal(url=url, title=title, price=price, originalPrice=original_price,
                        detail=detail.decode('utf8'),cityID=cityID, siteID=site[0].id)
            logging.info('%s OK', url)
        except:
            # Simple handling for the moment.
            logging.error("Error occured while saving data : %s", sys.exc_info())

    def test11_lookup_insert_transform(self):
        "Testing automatic transform for lookups and inserts."
        if DISABLE: return
        # San Antonio in 'WGS84' (SRID 4326)
        sa_4326 = 'POINT (-98.493183 29.424170)'
        wgs_pnt = fromstr(sa_4326, srid=4326) # Our reference point in WGS84

        # Oracle doesn't have SRID 3084, using 41157.
        if oracle:
            # San Antonio in 'Texas 4205, Southern Zone (1983, meters)' (SRID 41157)
            # Used the following Oracle SQL to get this value:
            #  SELECT SDO_UTIL.TO_WKTGEOMETRY(SDO_CS.TRANSFORM(SDO_GEOMETRY('POINT (-98.493183 29.424170)', 4326), 41157)) FROM DUAL;
            nad_wkt  = 'POINT (300662.034646583 5416427.45974934)'
            nad_srid = 41157
        else:
            # San Antonio in 'NAD83(HARN) / Texas Centric Lambert Conformal' (SRID 3084)
            nad_wkt = 'POINT (1645978.362408288754523 6276356.025927528738976)' # Used ogr.py in gdal 1.4.1 for this transform
            nad_srid = 3084

        # Constructing & querying with a point from a different SRID. Oracle
        # `SDO_OVERLAPBDYINTERSECT` operates differently from
        # `ST_Intersects`, so contains is used instead.
        nad_pnt = fromstr(nad_wkt, srid=nad_srid)
        if oracle:
            tx = Country.objects.get(mpoly__contains=nad_pnt) 
        else:
            tx = Country.objects.get(mpoly__intersects=nad_pnt)
        self.assertEqual('Texas', tx.name)
        
        # Creating San Antonio.  Remember the Alamo.
        sa = City(name='San Antonio', point=nad_pnt)
        sa.save()
        
        # Now verifying that San Antonio was transformed correctly
        sa = City.objects.get(name='San Antonio')
        self.assertAlmostEqual(wgs_pnt.x, sa.point.x, 6)
        self.assertAlmostEqual(wgs_pnt.y, sa.point.y, 6)

import csv
import os
import time
from models import City
from app import db


City.create_table(True)
i = 0
data_source = []
print(City.delete().execute())
with open("cp.csv", "r", encoding="utf-8") as csvfile:
    spamreader = csv.reader(csvfile, delimiter=";")

    for row in spamreader:
        data_source.append({"insee": row[0], "name": row[1], "postal_code": row[2], "label": row[3]})
        i += 1

        if len(data_source) > 100:
            print("+1")
            with db.atomic():
                City.insert_many(data_source).execute()
            data_source = []
            time.sleep(0.02)

    def test02_proxy(self):
        "Testing Lazy-Geometry support (using the GeometryProxy)."
        #### Testing on a Point
        pnt = Point(0, 0)
        nullcity = City(name='NullCity', point=pnt)
        nullcity.save()

        # Making sure TypeError is thrown when trying to set with an
        #  incompatible type.
        for bad in [5, 2.0, LineString((0, 0), (1, 1))]:
            try:
                nullcity.point = bad
            except TypeError:
                pass
            else:
                self.fail('Should throw a TypeError')

        # Now setting with a compatible GEOS Geometry, saving, and ensuring
        #  the save took, notice no SRID is explicitly set.
        new = Point(5, 23)
        nullcity.point = new

        # Ensuring that the SRID is automatically set to that of the 
        #  field after assignment, but before saving.
        self.assertEqual(4326, nullcity.point.srid)
        nullcity.save()

        # Ensuring the point was saved correctly after saving
        self.assertEqual(new, City.objects.get(name='NullCity').point)

        # Setting the X and Y of the Point
        nullcity.point.x = 23
        nullcity.point.y = 5
        # Checking assignments pre & post-save.
        self.assertNotEqual(Point(23, 5), City.objects.get(name='NullCity').point)
        nullcity.save()
        self.assertEqual(Point(23, 5), City.objects.get(name='NullCity').point)
        nullcity.delete()

        #### Testing on a Polygon
        shell = LinearRing((0, 0), (0, 100), (100, 100), (100, 0), (0, 0))
        inner = LinearRing((40, 40), (40, 60), (60, 60), (60, 40), (40, 40))

        # Creating a State object using a built Polygon
        ply = Polygon(shell, inner)
        nullstate = State(name='NullState', poly=ply)
        self.assertEqual(4326, nullstate.poly.srid) # SRID auto-set from None
        nullstate.save()

        ns = State.objects.get(name='NullState')
        self.assertEqual(ply, ns.poly)
        
        # Testing the `ogr` and `srs` lazy-geometry properties.
        if gdal.HAS_GDAL:
            self.assertEqual(True, isinstance(ns.poly.ogr, gdal.OGRGeometry))
            self.assertEqual(ns.poly.wkb, ns.poly.ogr.wkb)
            self.assertEqual(True, isinstance(ns.poly.srs, gdal.SpatialReference))
            self.assertEqual('WGS 84', ns.poly.srs.name)

        # Changing the interior ring on the poly attribute.
        new_inner = LinearRing((30, 30), (30, 70), (70, 70), (70, 30), (30, 30))
        ns.poly[1] = new_inner
        ply[1] = new_inner
        self.assertEqual(4326, ns.poly.srid)
        ns.save()
        self.assertEqual(ply, State.objects.get(name='NullState').poly)
        ns.delete()

    def get(self, action=None):
        if action:
            if action == "turn_download_on":
                turn_download_on()
            if action == "turn_download_off":
                turn_download_off()

        self.response.out.write("Admin page<br/><br/>")
        self.response.out.write('<a href="/gt/">Home</a><br/><br/>')
        self.response.out.write('<a href="/gt/admin/create_geo_trees">Create GeoTrees</a><br/><br/>')
        self.response.out.write('<a href="/_ah/admin">App Engine localhost admin</a><br/><br/>')
        self.response.out.write('<a href="/gt/admin/add_points">Add OSM points to GeoTree</a><br/>')
        self.response.out.write('<a href="/gt/admin/add_cities">Add cities to GeoTree</a><br/><br/>')
        self.response.out.write('<a href="/gt/admin/update_tiles">Update OSM GeoTree tiles</a><br/>')
        self.response.out.write('<a href="/gt/admin/update_cities_tiles">Update Cities GeoTree tiles</a><br/><br/>')
        if is_download_on():
            self.response.out.write('<a href="/gt/admin/turn_download_off">Turn OSM Download OFF</a><br/><br/>')
        else:
            self.response.out.write('<a href="/gt/admin/turn_download_on">Turn OSM Download ON</a><br/><br/>')

        if action:
            if action == "create_geo_trees":
                gt = GeoTree.get(gt_key_name="osm")
                if not gt:
                    gt = GeoTree(key_name="osm", max_z=config.max_z_osm, min_z=config.min_z_osm)
                    gt.put()
                    self.response.out.write("\n\nInfo: Created osm GeoTree.")
                else:
                    gt.max_z = config.max_z_osm
                    gt.min_z = config.min_z_osm
                    gt.put()
                    self.response.out.write("\n\nInfo: OSM GeoTree exists.")
                gt = GeoTree.get(gt_key_name="cities")
                if not gt:
                    gt = GeoTree(key_name="cities", max_z=config.max_z_cities, min_z=config.min_z_cities)
                    gt.put()
                    self.response.out.write("\nInfo: Created cities GeoTree.")
                else:
                    gt.max_z = config.max_z_cities
                    gt.min_z = config.min_z_cities
                    gt.put()
                    self.response.out.write("\nInfo: Cities GeoTree exists.")
            if action == "add_points":
                batch = OSMPOI.all().filter("is_in_tree =", False).fetch(self._BATCH_ADD_SIZE)
                if batch:
                    GeoTree.insert_points_list(batch, max_z=17, gt_key_name="osm")
                    self.response.out.write("\n\nInfo: added %d points" % len(batch))
                    for p in batch:
                        p.is_in_tree = True
                    db.put(batch)
                    taskqueue.add(url="/gt/admin/add_points", method="GET")
                else:
                    if GeoTree.exists(gt_key_name="osm"):
                        self.response.out.write("\n\nInfo: no POIs to add.")
                        taskqueue.add(url="/gt/admin/update_tiles", method="GET")
                    else:
                        self.response.out.write("\n\nInfo: GeoTree does not exist.")
            if action == "add_cities":
                batch = City.all().filter("is_in_tree =", False).fetch(self._BATCH_ADD_SIZE)
                if batch:
                    GeoTree.insert_points_list(batch, gt_key_name="cities")
                    self.response.out.write("\n\nInfo: added %d cities" % len(batch))
                    for p in batch:
                        p.is_in_tree = True
                    db.put(batch)
                else:
                    if GeoTree.exists(gt_key_name="cities"):
                        self.response.out.write("\n\nInfo: no cities left out of tree")
                    else:
                        self.response.out.write("\n\nInfo: GeoTree does not exist")
            if action == "update_tiles":
                message = GeoTree.update_tiles(count=self._BATCH_UPDATE_SIZE, gt_key_name="osm")
                if message:
                    if "nothing to update" in message:
                        self.response.out.write("<br/>" + message)
                else:
                    taskqueue.add(url="/gt/admin/update_tiles", method="GET")
            if action == "update_cities_tiles":
                message = GeoTree.update_tiles(count=self._BATCH_UPDATE_SIZE, gt_key_name="cities")
                if message:
                    self.response.out.write("<br/>" + message)
                else:
                    self.response.out.write("\n\nInfo: updated tiles")
            # memcaching is not used at the moment
            if action == "clear_cache":
                memcache.flush_all()
                self.response.out.write("<br/>All memcache entries are deleted.")

def home(request):
    # create a location for no particular reason
    if City.objects.count() == 0:
        City.find("Cambridge", "MA")
    return render_to_response('home.html')

from app import app
from models import City
import views

if __name__ == '__main__':
    City.create_table(True)
    app.run()

def city_list():
    obj_list = City.select().order_by('name')
    return object_list('city_list.html', obj_list, "obj_list")

def upload(request):
    """
        Uploads the receipt
        
        :url: /shoebox/upload/
        :param POST['email']: email identifying user
        :param POST['business_name']: i.e. McDonalds (blank)
        :param POST['address']: business address (blank)
        :param POST['location']: i.e. JFK International (blank)
        :param POST['phone']: phone number (blank)
        :param POST['city']: city (blank)
        :param POST['state']: state (blank)
        :param POST['purchase_date']: purchase date in NeatReceipts format
        :param POST['tax']: tax (blank)
        :param POST['tip']: tip (blank)
        :param POST['amount']: total amount
        :param POST['payment_method']: Visa, Master, Cash etc
        :param POST['category']: NeatReceipts category
        :param FILES['img']: Receipts image

        :rtype: JSON
        
        ::
        
            #: if success in posting returns id of created or update object in string format
            {'result': 'id'}
            #: if failed
            {'result': '-1'}
            #: if request was not POST
            {'result': '0'}
    """
    if request.method == 'POST':
        form = ReceiptUploadForm(request.POST, request.FILES)
        if form.is_valid():
            instance = form.save(commit=False)
            # assign to the current user uploading data
            instance.user, created = OTNUserTemp.objects.get_or_create(email=request.POST['email']) 
            instance.save()
            
            receipt = DetailReceipt(basic=instance)
            if 'business_name' in request.POST:
                b = Business(name=request.POST['business_name'])
                if 'location' in request.POST:
                    b.location = request.POST['location']
                if 'phone' in request.POST:
                    b.phone = request.POST['phone']
                if 'address' in request.POST:
                    b.address = request.POST['address']
                if 'city' in request.POST:
                    c = City(name=request.POST['city'], state=request.POST['state'])
                    c.save()
                    b.city = c
                b.save()
                receipt.business = b
        
            if 'category' in request.POST:
                cat, created = Category.objects.get_or_create(name=request.POST['category'])
                receipt.category = cat
            if 'tax' in request.POST: 
                receipt.tax = request.POST['tax']
            if 'tip' in request.POST:
                receipt.tip = request.POST['tip']
            if 'payment_method' in request.POST:
                pmethod = request.POST['payment_method'].lower()
                if pmethod.find('cash') != -1:
                    receipt.payment_method = receipt.CASH
                elif pmethod.find('amex') != -1:
                    receipt.payment_method = receipt.AMEX
                elif pmethod.find('visa') != -1:
                    receipt.payment_method = receipt.VISA
                elif pmethod.find('master') != -1:
                    receipt.payment_method = receipt.MASTER
                elif pmethod.find('discover') != -1:
                    receipt.payment_method = receipt.DISCOVER
                else:
                    receipt.payment_method = receipt.CASH

            receipt.save()
            return JSONHttpResponse({'result':str(receipt.id)})
        else:
            return JSONHttpResponse({'result':'-1', 'form_errors':str(form.errors)})
    else:
        return JSONHttpResponse({'result':'0'})